Advanced Query

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.

__advanced-analytics-2-image-unique-image-unique

__advanced analytics

<h3>Installation</h3>
To install the Advanced Formula add-on:
Windows:
<ol>
<li>Extract the .zip folder.</li>
<li>Install the client-side component:Place the ./plugin/advancedFormula folder in C:/Program Files/Sisense/app/plugins/
<ul>
<li>Create the folder (if it does not exist).</li>
</ul>
</li>
<li>Configure the client-side component as described <a href="https://docs.google.com/document/d/1yZnWrP2TriUO9_io-KAaCHjulnpag2J6ADrvyimC4iM/edit#heading=h.tdonuukznmp6">below</a>.</li>
<li>Run the ./PSE.Sisense.AdvancedFormula_version.msi installer to install the Advanced Formula microservice.</li>
<li>Restart the Sisense.AdvancedFormula Windows service to apply configuration changes.</li>
<li>Install the server-side component:
<ul>
<li>Open the <a href="https://documentation.sisense.com/latest/administration/system-settings/configmgr.htm#gsc.tab=0">Configuration Manager</a>.</li>
<li>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.</li>
<li>Enable the server-side plugins by enabling the serverSidePlugins.enabled parameter.</li>
<li>Copy the folder ./plugin/advancedFormulaInterceptor into the path specified in the serverSidePlugins.dirPath box.</li>
<li>Click Save Changes.</li>
</ul>
</li>
<li>Restart api-gateway Windows service.</li>
<li>Refresh your dashboard.</li>
</ol>
<h4>Linux: </h4>
<ol>
<li>Extract the .zip folder.</li>
<li>Install the client-side component: 
Place the  ./plugin/advancedFormula folder in /opt/sisense/storage/plugins/.</li>
<li>Configure the client-side component, as described <a href="https://docs.google.com/document/d/1yZnWrP2TriUO9_io-KAaCHjulnpag2J6ADrvyimC4iM/edit#heading=h.tdonuukznmp6">below</a>.</li>
<li>Place the external-plugin component ./plugin/advancedFormulaService/src/features/advancedFormula folder into /opt/sisense/storage/external-plugins/apiPlugins/plugins/.</li>
<li>(For versions L2021.4 and earlier) Restart external-plugins pod to apply configuration changes.</li>
<li>Install the server-side component
<ul>
<li>Open the <a href="https://documentation.sisense.com/latest/administration/system-settings/configmgr.htm#gsc.tab=0">Configuration Manager</a>.</li>
<li>Scroll to the bottom of the page and click Show Advanced.</li>
<li><img loading="lazy" class="alignleft" src="https://cdn.sisense.com/wp-content/uploads/Advanced-Formula_1.png" alt="" width="1234" height="1203" /></li>
<li>Expand the Server Side Plugins section and enable the server-side plugins.</li>
<li><img loading="lazy" class="alignleft" src="https://cdn.sisense.com/wp-content/uploads/Advanced-Formula_2.png" alt="" width="980" height="447" /></li>
<li>Click Save.</li>
<li>Copy folder ./plugin/advancedFormulaInterceptor to /opt/sisense/storage/serverSidePlugins.</li>
<li>(For versions L2021.4 and earlier) Restart the external-plugins pod.</li>
</ul>
</li>
<li>Refresh your dashboard</li>
</ol>
<h3>Configuration</h3>
Configure the client-side plugin by the included `config.6.js`.`supportedWidgetTypes` &#8211; array of widget types, where the Advanced Formula functionality is enabled.
Example: 
&#8220;`javascript 
const config = { 
// List of widgets type with enabled advanced formulas functionality 
supportedWidgetTypes: [&#8220;pivot&#8221;, &#8220;textBox&#8221;, &#8220;pivot2&#8221;], 
};
<h4>Implementation:</h4>
<ol>
<li style="font-weight: 400;">Create a new Pivot widget, or edit the existing Pivot widget.</li>
<li style="font-weight: 400;">Add all of the items for the rows/values/columns that are relevant to this widget.</li>
<li style="font-weight: 400;">Click the magic wand to add the CASE/IF formula from the Sisense Formula Editor.</li>
<li style="font-weight: 400;">Click Apply.</li>
</ol>
<h4>Usage:</h4>
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 &lt;condition1&gt; THEN &lt;result1&gt; 
WHEN &lt;condition2&gt; THEN &lt;result2&gt; 
ELSE &lt;result3&gt; 
END
If-Else statements:
IF (&lt;condition1&gt;) 
&lt;result1&gt; 
ELSE IF &lt;condition2&gt; 
&lt;result2&gt; 
ELSE &lt;result3&gt; 
END
Note:
<ul>
<li>&lt;condition&gt; – should contain dimension calculations and conditions</li>
<li>&lt;result&gt; – should contain only one calculations wrapped in SISENSE function and any amount of string concatenations</li>
</ul>
Examples:
CASE function
CASE
WHEN [Total Quantity]&gt;2500 THEN left( SISENSE( ([# of unique Age 
Range] +1)),5)  + upper(‘result!’)
WHEN [Total Quantity]&lt;2000 THEN ‘Final data: ‘ + SISENSE(  [# of unique Gender] )
ELSE upper(‘not valid!’)
END
IF function
IF ([Total Cost] &gt; 10 OR [# of unique Category]&gt;2 AND [# of unique Category] &lt; 3)
Upper(‘value = ‘) + Sisense([Total Cost])
ELSE IF ([Total Cost] &gt; 30)
‘some value’
<h3>Limitations:</h3>
<ol>
<li style="font-weight: 400;">‘Filter by value’ (filter button in measure tile) on advanced formulas are not supported.</li>
<li style="font-weight: 400;">‘Download CSV’ functionality is not supported for a widget with added measures with advanced formulas enabled.</li>
<li style="font-weight: 400;">‘Quick Functions’ (from the Measure Tile menu) on advanced formulas are not supported.</li>
</ol>
<h3>Supported functions:</h3>
<ul>
<li>Regarding CASE, IF, SISENSE, UPPER, LEFT and ” for strings; read the function descriptions in the Formula Editor.</li>
<li>Advanced CASE/IF functions are restricted to ‘top level’ functions in a formula.</li>
<li>Advanced SISENSE, UPPER, LEFT functions and ” are restricted for use only in advanced CASE/IF functions.</li>
</ul>

Get the Add-on

Leave Feedback