Advanced Formula

By sisense

The advanced formula add-on allows users to use an advanced CASE/IF formula within the Sisense formula editor for the pivot widget in order to manipulate math operations and strings. The add-on adds new functions to the formula editor which can be enabled by pressing the magic wand button within the formula editor. When creating new formulas, users can create CASE/IF functions which combine math operations and string concatenation to represent data in a more meaningful way.

To install the Advanced Formula add-on:

  1. Download the attachment.
  2. Extract the .zip folder into the plugins folder. If the folder does not exist, create it prior to extracting the .zip file.
    For V7.1 and earlier: C:\Program Files\Sisense\PrismWeb\plugins
    For V7.2 and later: C:\Program Files\Sisense\app\plugins
  3. Install windows msi ‘advancedFormulaService.msi’. (Installer will create windows service for the plugin)
  4. Restart all Sisense services

Implementation:

  1. Create a new pivot widget or edit existing pivot widget.
  2. Add all items for rows/ values/ columns that are relevant to this widget
  3. By pressing the Magic Wand button, add CASE/IF formula from the Sisense formula editor
  4. Apply your changes

Configuration:

To use new CASE/IF formulas enable the Magic Wand button and pick functions from the “Advanced functions” list.

Advanced formulas supports the next format:

CASE

WHEN <condition1> THEN <result1>

WHEN <condition2> THEN <result2>

ELSE <result3>

END

————————-

IF (<condition1>)

<result1>

ELSE IF <condition2>

<result2>

ELSE <result3>

END

<condition> – should contain dimension calculations and condition

<result> – should contain only one calculations wrapped in SISENSE function and any amount of string concatenations

 

Example:

CASE function:

CASE

WHEN [Total Quantity]>2500 THEN left( SISENSE( ([# of unique Age Range] +1)),5)  + upper(‘result!’)

WHEN [Total Quantity]<2000 THEN ‘Final data: ‘ + SISENSE(  [# of unique Gender] )

ELSE upper(‘not valid!’)

END

IF function:

IF ([Total Cost] > 10 OR [# of unique Category]>2 AND [# of unique Category] < 3)

Upper(‘value = ‘) + Sisense([Total Cost])

ELSE IF ([Total Cost] > 30)

‘some value’

Limitations:

  1. ‘Filter by value’ (filter button in measure tile) on advanced formulas are not supported.
  2. ‘Download CSV’ functionality is not supported for widget with added measures with advanced fomulas enabled.
  3. ‘Quick functions’ (from measure tile menu) on advanced formulas are not supported.

Supported functions:

CASE, IF, SISENSE, UPPER, LEFT and ” for strings – Please read functions descriptions in the formula editor. Advanced CASE/IF functions restricted to be top level function in a formula. Advanced SISENSE, UPPER, LEFT functions and ” are restricted to be used only in advanced CASE/IF functions.

This is a premium Sisense add-on. For pricing details please get in touch with your CSM: Get the Add-On