Advanced Formula

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.