Advanced Formula
- Overview
- Technical Details
- Pricing
- Release Notes
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:
- Download the attachment.
- 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 - Install windows msi ‘advancedFormulaService.msi’. (Installer will create windows service for the plugin)
- Restart all Sisense services
Implementation:
- Create a new pivot widget or edit existing pivot widget.
- Add all items for rows/ values/ columns that are relevant to this widget
- By pressing the Magic Wand button, add CASE/IF formula from the Sisense formula editor
- 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:
- ‘Filter by value’ (filter button in measure tile) on advanced formulas are not supported.
- ‘Download CSV’ functionality is not supported for widget with added measures with advanced fomulas enabled.
- ‘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.
21/8/2019: Fixed compatibility issue with the Tabber widget plugin
15/7/2019: Added compatibility with Sisense 8.1
22/4/2020: Added compatibility with Sisense 8.2
19/5/2020: Added compatibility with Sisense 8.2.1
16/6/2020: Added compatibility with Sisense 8.2.2
09/7/2020: Fixed issue : Incorrect Data in Excel export of Widget with Advanced Functions
20/1/2021: Added compatibility with:
- Sisense Windows 8.2.3
- Sisense Windows 8.2.4
- Sisense Windows 8.2.5
- Sisense Linux 8.2.4
- Sisense Linux 8.2.5
- Sisense Linux 8.2.6
Premium
