Advanced Formula

By Sisense

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>)

ELSE IF <condition2>

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.

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

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

18/2/2021: Added compatibility with L2021.1.1

26/3/2021: Added next changes:

Added compatibility with Windows 2021.2
Fixed issue with wrong section name in Advanced Functions list

7/4/2021: Added compatibility with:

Sisense Windows 2021.2
Sisense L2021.1.4
Sisense L2021.3.1