The Advanced Formula add-on enables Sisense users to use an advanced CASE/IF formula within the Sisense Formula Editor to manipulate math operations and strings for the Pivot widget.
The add-on brings new functions to the Formula Editor, which is enabled by clicking 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.
Note: Only numeric comparison is supported. String comparison is not supported.
Installation
To install the Advanced Formula add-on:
Windows:
- Extract the .zip folder.
- Install the client-side component:Place the ./plugin/advancedFormula folder in C:/Program Files/Sisense/app/plugins/
- Create the folder (if it does not exist).
- Configure the client-side component as described below .
- Run the ./PSE.Sisense.AdvancedFormula_version.msi installer to install the Advanced Formula microservice.
- Restart the Sisense.AdvancedFormula Windows service to apply configuration changes.
- Install the server-side component:
- Open the Configuration Manager.
- Click the Sisense logo five times to show the complete list of service configuration options on the left, and navigate to the api-gateway tab.
- Enable the server-side plugins by enabling the serverSidePlugins.enabled parameter.
- Copy the folder ./plugin/advancedFormulaInterceptor into the path specified in the serverSidePlugins.dirPath box.
- Click Save Changes.
- Restart api-gateway Windows service.
- Refresh your dashboard.
Linux:
- Extract the .zip folder.
- Install the client-side component:
Place the ./plugin/advancedFormula folder in /opt/sisense/storage/plugins/.
- Configure the client-side component, as described below.
- Place the external-plugin component ./plugin/advancedFormulaService/src/features/advancedFormula folder into /opt/sisense/storage/external-plugins/apiPlugins/plugins/.
- (For versions L2021.4 and earlier) Restart external-plugins pod to apply configuration changes.
- Install the server-side component
- Open the Configuration Manager.
- Scroll to the bottom of the page and click Show Advanced.
- Expand the Server Side Plugins section and enable the server-side plugins.
- Click Save.
- Copy folder ./plugin/advancedFormulaInterceptor to /opt/sisense/storage/serverSidePlugins.
- (For versions L2021.4 and earlier) Restart the external-plugins pod.
- Refresh your dashboard
Configuration
Configure the client-side plugin by the included `config.6.js`.`supportedWidgetTypes` – array of widget types, where the Advanced Formula functionality is enabled.
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 the existing Pivot widget.
- Add all of the items for the rows/values/columns that are relevant to this widget.
- Click the magic wand to add the CASE/IF formula from the Sisense Formula Editor.
- Click Apply.
Usage:
To use the new CASE/IF formulas, enable the Magic Wand button and select functions from the Advanced Functions list.
Advanced formulas support the following formats:
Case-If statements:
CASE
WHEN <condition1> THEN <result1>
WHEN <condition2> THEN <result2>
ELSE <result3>
END
If-Else statements:
IF (<condition1>)
<result1>
ELSE IF <condition2>
<result2>
ELSE <result3>
END
Note:
- <condition> – should contain dimension calculations and conditions
- <result> – should contain only one calculations wrapped in SISENSE function and any amount of string concatenations
Examples:
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.
- ‘Quick Functions’ (from the Measure Tile menu) on advanced formulas are not supported.
Supported functions:
- Regarding CASE, IF, SISENSE, UPPER, LEFT and ” for strings; read the function descriptions in the Formula Editor.
- Advanced CASE/IF functions are restricted to ‘top level’ functions in a formula.
- Advanced SISENSE, UPPER, LEFT functions and ” are restricted for use only in advanced CASE/IF functions.
This is a premium Sisense add-on. For pricing details please get in touch with
your CSM
Version 1.3.8 – Nov. 21, 2023
- Bug fix: shared formula cannot be saved if an add-on is installed
- Bug fix: add-on causes “no result” for pivot
v1.3.4 – Aug. 2, 2022
- Added support for CSV export
- Added support for Export to Excel v2
L2022.3 – Mar. 11, 2022
- Added support for L2022.3
L2022.2 – Feb. 10, 2022
- Added support for L2022.2
- Installation steps have been changed to support installation with scripts
L2022.1 – Jan. 21, 2022
L2021.12.0 – Dec. 9, 2021
L2021.11.0 – Oct. 26, 2021
W2021.9 – Oct. 06, 2021
L2021.10.0 – Sept. 16, 2021
L2021.9.0 – Sept. 02, 2021
L2021.8.0 and Windows W2021.4 – June 02, 2021
- Added compatibility – Fixed conflict with Metadata plugin
Windows 2021.4 – June 02, 2021
- Fixed conflict with Metadata plugin
Windows W2021.2, L2021.1.4, L2021.3.1 – April 7, 2021
Windows 2021.2 – March 26, 2021
- Fixed issue with wrong section name in Advanced Functions list
L2021.1.1 – Feb. 18, 2021
Windows 8.2.3, Windows 8.2.4, Windows 8.2.5, Linux 8.2.4, Linux 8.2.5, Linux 8.2.6 – Jan. 20, 2021
- Fixed issue: Incorrect Data in Excel export of Widget with Advanced Functions – July 09, 2020
Version 8.2.2 – June 16, 2020
Version 8.2.1 – May 19, 2020
Version 8.2 – April 22, 2020
Version 8.1 – July 15, 2019
Aug.21, 2019 – Version Fixed compatibility issue with the Tabber widget plugin
