Filtered Measure Plugin (Parameters)
*This plugin is officially supported by Sisense. You can find the latest version of this plugin on the Sisense Add-Ons page.
Introduction
By default, filter selections in Sisense can be defined on the Dashboard OR Widget level. The “parameters” plugin is a functionality that makes it possible to create additional selection states. This means that you’re able to show two (or more) different sets of selections in a single widget.
This feature is very handy when you want to perform comparative analysis. With the use of this plugin, two selections can be shown in a single view – which makes it easier for us to compare the two.
This functionality should be used with caution as it can cause great confusion with end-users as there is no automatic on-screen indication that fields or values are in. It is up to the Sisense contributor to provide this information to the consumer adding informative description to the widget.
Business Case
An eCommerce agency needs to create a dashboard that provides sales analysis across years. One of the main purposes of this dashboard will be to compare sales of their categories in different years. In order to provide side-by-side comparisons that the analysts can change dynamically, we will need to create two different filers: Year 1, and Year 2. These filters will represent two different year selections that can easily be changed by the user.
Implementation Steps:
Step 1 - Download the Filered Measures plugin here and unzip the contents into your C:\Program Files\Sisense\PrismWeb\plugins\ (Sisense 7.1.3 and below) or C:\Program Files\Sisense\app\plugins (Sisense 7.2+). If the plugins folder doesn't exist, just create it.
Step 2 - Create a duplication of the Date field within the ElastiCube
Step 3 - Use the 2 date fields you’ve created and create Year 1 and Year 2 filters within the dashboards:
Step 4 - Create a pivot table that calculates Revenue per Category, make sure it's connected to Dashboard filters:
Step 5 - Go the the Revenue formula editor and add the “Years in Date” field to the formula:
Step 6 - Change the name of the field to the name of the required filter (=the one you want to affect the value) and add ‘@’ symbol before:
Step 7 - Rename the field to meaningful name (for example: Year 1 Revenue)
Step 8 - Repeat steps 5-7 for “Year 2”:
Step 9 - Now “Year 1 Revenue” if affected by "Year 1" filter and “Year 2 Revenue” if affected by “Year 2” filter
Step 10 - In order to create Growth calculation, simply use the following syntax:
Step 11 - Once you create the Growth measure, you can use it in a numeric indicator and bar chart (ecdata and dash file can be found here filteredMeasure_Example.zip)
Updates:
March 12th - updated the plugin to support version 7.0.1, including:
- Auto Sync of all filters for a specific measure using the measure menu button and clicking the option "Sync Measure with filters" - automatically adds all filters to the measure and adds a prefix of '@'
- Auto Sync of all filters with all measures in the widget using the widget menu button (editor page) and clicking the option "Sync dashboard filters with measures" - automatically adds all filters to all measures and adds a prefix of '@'
- Support in Prev Year/s, Just rename the field (in the formula) to '@.PREV.[filter name]' or to '@.PREV.[number].[filter name]' to get results of the previous [number] of years
- Support for cascading filters
-
Thank you Odir, the broken link has been updated.
Please note that the plugin has been updated, the post has been updated accordingly.
With the new plugin, related filters should remain active and affect widget.
If you wish to replace the previous FilteredMeasure plugin with the new one, please make sure to remove the old plugin folder.
-
Hi Konstantino,
Can you please attach the ecdata and the dash file / send it to me? (hila.kantor@sisense.com)
-
Hey Ramon,
I have a situation in which I only want to have a one-dashboard-filter-for-all where I can filter by specific items in a field for one widget and filter by everything but those items in another widget. In essence, what I'm excluding in one widget is being included in another. Below is what I did:
Step 1: Install Filtered Measure Plugin as instructed above and for a field called "Airline", duplicate it and have a field called "Airline1". Add both fields in the dashboard filter as instructed and name them "Controllable Airlines" and "Uncontrollable Airlines" . Since Controllable Airlines outnumber Uncontrollable Airlines it is easiest to exclude the uncontrollables.
Step 2: In one of my column chart widgets I apply the magic of the filtered measure plugin on one of the values (USD Controllable Fulfillment)
Step 3: Use the "Uncontrollable Airlines" filter on a couple of widgets.In the grand scheme of things this is fine. But it is not fluid. The viewer of this dashboard has to deselect some items in one filter and select the exact same items in another filter.
So my question is is there a way (perhaps some JS) in which I can keep only the "Controllable Airlines" filter to apply the filtered measure in that first widget and simultaneously in the other widget by inversely selecting everything but what's selected in the filter?
I hope this question makes sense.
Thanks,Ayanda
-
Hi Ayanda, since they're exclusive wouldn't the sum of controllable + uncontrollable = total? In that case maybe try something like this for the relevant uncontrollable metrics:
[VALC USA Fulfillment]-([VALC USA Fulfillment],[@Controllable Airlines])
Only 1 filter (controllable) needed!
-
Thanks Evan,
I initially tried to use that logic but your solution helped me reach the answer I was looking for. Some probability math was also required. So the solution was:
([VALC USA Fulfillment]-([a weight for uncontrollable airlines]*[uncontrollable fulfillment])/[a weight for controllable fulfillment]
Regards,
Ayanda
-
Ayanda - Existing formulas using this plug-in do work fine in v7, but there is currently a v7 bug renaming fields in formulas that makes it impossible to create any new formulas or edit any existing formulas that leverage this plug-in.
I suggest to avoid it with v7 until SiSense confirms a fix to renaming fields in formulas.
-
As Evan mentions, there is an issue in Sisense 7.0 where the formula cannot be renamed through the web UI (Step 6). This issue will be resolved with Sisense 7.1.
As a workaround in the meantime, export the .dash file and open it in a text editor. The dash file is a JSON structure that determines the objects and properties of your dashboard.
Navigate to widgets[i].metadata.panels[i].items[i].jaql.context[i] - this is within a section that defines the JAQL for a widget measure. Navigate to the "title" key. Prepend @ to the existing value. For example, to complete Step 6 above, navigate to:
"title": "Year1"
Change it to:
"title": "@Year1"
After making the change, save the .dash file and reimport it (and select Overwrite as the method).
Extract from a sample .dash file
-
Hi all,
We've updated the plugin to support version 7.0.1, including:
- Auto Sync of all filters for a specific measure using the measure menu button and clicking the option "Sync Measure with filters" - automatically adds all filters to the measure and adds a prefix of '@'
- Auto Sync of all filters with all measures in the widget using the widget menu button (editor page) and clicking the option "Sync dashboard filters with measures" - automatically adds all filters to all measures and adds a prefix of '@'
- Support in Prev Year/s, Just rename the field (in the formula) to '@.PREV.[filter name]' or to '@.PREV.[number].[filter name]' to get results of the previous [number] of years
- Support for cascading filters
Thanks,
Oxana
-
I've attempted to utilize this widget but experiencing some issues. In my case, I have created a duplicate field for Country in an effort to display a comparison between Domestic (US) and International. Unfortunately, when the filter selections are made (County: United States, Country1: Not United States) the filters cancel each other and end up displaying "No Results". Note: I am on Version 7.1.0.12003
Please sign in to leave a comment.
Comments
25 comments