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 use new CASE/IF formulas enable the Magic Wand button and pick functions from the “Advanced functions” list.
Advanced formulas supports the next format:
WHEN <condition1> THEN <result1>
WHEN <condition2> THEN <result2>
ELSE IF <condition2>
<condition> – should contain dimension calculations and condition
<result> – should contain only one calculations wrapped in SISENSE function and any amount of string concatenations
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!’)
IF ([Total Cost] > 10 OR [# of unique Category]>2 AND [# of unique Category] < 3)
Upper(‘value = ‘) + Sisense([Total Cost])
ELSE IF ([Total Cost] > 30)
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.