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.

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

#### 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.
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.