Advanced Formula

By Sisense

Overview
Technical Details
Pricing
Release Notes

To manipulate math operations and strings, the advanced formula add-on allows users to use an advanced CASE/IF formula within the Sisense formula editor for the pivot widget.

The add-on adds new functions in the formula editor that can be enabled by using the magic wand in the formula editor.

When creating new formulas, users can create CASE/IF functions that combine math operations and string concatenation to represent data in a more meaningful way.

Installation

Windows:

Download the attachment and extract the .zip folder.
Install the client-side component in the /plugin/advancedFormula folder in /opt/sisense/storage/plugins/. You may need to add this folder.
Configure the client-side component by running the ./PSE.Sisense.AdvancedFormula_version.msi installer, which installs the Advanced Formula microservice.
Restart the Sisense.AdvancedFormula Windows service to apply the configuration changes.
Install the the server-side component as follows:
Open the Configuration Manager and click the Sisense logo five times to display the full list of services configurations.
From the menu, select Services>api-gateway.
Scroll down to the Server Side Plugins area and enter ./plugin/advancedFormulaInterceptor in the serverSidePlugins.dirPath field and enable Server Side Plugins Enable.
Click Save Changes.
Restart api-gateway Windows service.
Refresh your dashboard.

Linux:

Download the attachment and extract the .zip folder.
Install the client-side component in the /plugin/advancedFormula folder in /opt/sisense/storage/plugins/. You may need to add this folder.
To configure the client-side component, copy the external-plugin component ./plugin/advancedFormulaService/src/features/advancedFormula folder into /opt/sisense/storage/external-plugins/apiPlugins/plugins/.
(For versions lower than L2021.5) Restart the Sisense.AdvancedFormula Windows service to apply the configuration changes.
To install the the server-side component:
Open the Configuration Manager and click the Sisense logo five times to display the full list of services configurations.
Scroll to the bottom of the page and click Show Advanced.
Select Server Side Plugins and enable the Server Side Plugins Enable option.

Click Save.
Copy folder ./plugin/advancedFormulaInterceptor to /opt/sisense/storage/serverSidePlugins.
(For version lower than L2021.5) Restart the external-plugins pod.
Refresh your dashboard.

Client-side Configuration:

Configure the client-side plug-in by using the included config.6.js file.
To enable the Advanced Formula functionality on specific widgets, you must enter the widgets in the`supportedWidgetTypes` parameter.

Example:
```javascript
const config = {
// List of widgets type with enabled advanced formulas functionality
supportedWidgetTypes: ["pivot", "textBox", "pivot2"],
};

Implementation:

Create a new pivot widget or edit existing pivot widget.
Add all items for rows/values/columns that are relevant to this widget
Use the Magic Wand button to add CASE/IF formula from the Sisense formula editor
Apply your changes

Usage:

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-If statements:

‘CASE

WHEN <condition1>  THEN <result1>

WHEN <condition2> THEN <result2>

ELSE <result3>

END’

-------------------------

‘IF (<condition1>)

ELSE IF <condition2>


                          <result2>

ELSE <result3>

END’

<condition>: Contains dimension calculations and condition
<result>: Contains 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:

For CASE, IF, SISENSE, UPPER, LEFT and ” for strings, 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

02/6/2021: Added next changes:

Added compatibility with Windows 2021.4
Fixed conflict with Metadata plugin

02/6/2021: Added next changes:

Added compatibility with L2021.8.0
Added compatibility with Windows W2021.4
Fixed conflict with Metadata plugin

02/9/2021: Added next changes:

Added compatibility with L2021.9.0

16/09/2021: Added next changes:

Added support for L2021.10.0

06/10/2021: Added next changes:

Added support for W2021.9