Interactive Time Period Comparison with BloX & Custom Actions
Here is a use case on how to leverage BloX widgets in Sisense to create an interactive dashboard for comparing any KPI between two different periods. This solution allows users to select two months (or any two time periods with minor tweaking) and dynamically calculates the percentage change in the KPI between these selected months.
Overview
Dashboard with the Input Widget and Display Widget created using BloX
The dashboard consists of two BloX widgets:
- Input Widget: Allows users to select two different months (M1 and M2) from dropdown menus. This widget also contains an "Apply" button which triggers the KPI calculation, behind the scenes.
- Display Widget: Displays the percentage change in the KPI between the selected months and also shows a subtext indicating the compared periods.
Pre-Reqs
The only Pre-req for this solution to work is, you need to have your date field duplicated. For example, if the date field that's used for a KPI Calculation is "event_date", then duplicate it on the datamodel and name it "event_date_compare". The reason for this duplication is to power the two dropdowns independently.
How It Works
1 - Input Widget Configuration: (BloX Template file attached: momCompareInput.json)
- Two dropdown menus for month selection. Each one has its own id - "month" and "monthCompare" which will be used within the BloX Action code to retrieve the values and use it further for KPI calculation.
- Each dropdown is tied to a date field which are added to the "Items" panel of the widget. "Month" is the granularity chosen and the titles of these two items are "month" and "monthCompare"
"Items" panel of the Input Widget
- Each dropdown is tied to a date field which are added to the "Items" panel of the widget. "Month" is the granularity chosen and the titles of these two items are "month" and "monthCompare"
- An "Apply" button that triggers a custom BloX action - "momRateCompare".
- Three input parameters are defined in the "editor" of the BloX Widget, within the Action block
- monthField - "string" - Name of the date field from the datamodel that's used for the KPI Calculation and which is tied to one of the dropdowns
- monthCompareField - "string" - Name of the duplicated date field from the datamodel that's tied to one of the dropdowns
- widgetToModify - [Array of "strings"] - The widgetId of the Display BloX widget. This is to ensure both the Input Widget and the Display Widget are connected.
The Action Block with the three Input Parameters
- Two dropdown menus for month selection. Each one has its own id - "month" and "monthCompare" which will be used within the BloX Action code to retrieve the values and use it further for KPI calculation.
2- Custom BloX Action: (Action Code: see below)
- Captures the selected months (M1 and M2) from the dropdowns upon clicking "Apply".
- Converts the month selection into the required format to be added as a filter.
- Passes these values to the formula expression as the month filters in the Display Widget.
- Updates the subtext of the Display Widget to show the comparison months from the selected month values.
- Saves the changes to the Display Widget and refreshes it.
/* Getting the BloX Widget which shows the KPI value and get the formula's elements
i.e., the measured value elements */
const widgetId = payload.data.widgetToModify[0];
const widget = ((prism.activeDashboard.widgets.$$widgets).filter(widget => widget.oid == widgetId))[0];
const monthField = payload.data.monthField;
const monthCompareField = payload.data.monthCompareField;
var formulaContext = widget.metadata.panels[1].items[0].jaql.context;
//console.log(formulaContext);
// Getting the Dropdown values
var m = (payload.data.month) + "-01T00:00:00";
var mc = (payload.data.monthCompare) + "-01T00:00:00";
//console.log(m);
//console.log(mc);
//Setting the value of triggered_at as the SECOND Dropdown Value
var f1 = Object.keys(formulaContext).find(key => formulaContext[key].column === monthField);
formulaContext[f1].filter.members[0] = m;
//Setting the value of triggered_at_compare as the FIRST Dropdown Value
var f2 = Object.keys(formulaContext).find(key => formulaContext[key].column === monthCompareField)
formulaContext[f2].filter.members[0] = mc;
//Constructing and Setting the Subtitle text on the KPI Widget
var subTitle = 'Comparing ' + ((payload.data.month)) + " vs " + ((payload.data.monthCompare));
widget.style.currentCard.body[0].items.filter(i => i.id == "blox_text")[0]
.text = subTitle;
//Saving the changes and refreshing the widget
widget.changesMade('BloX-Actions', ['metadata', 'style']);
widget.refresh();
- 3 - Output Widget Configuration: (BloX Template file attached: momCompareDsply.json)
- Calculates the percentage change in the KPI between M1 and M2 using the formula below: the formula is based on calculating the % change in Engagement Rate, where
Engagement Rate = Engaged Clicks / Total Clicks)
The above formula is translated into Sisense formula expression using the measured value filters.
Sisense Formula Expression using Measured Value Filters to calculate the % Change in Rate
This formula is added to the "Values" panel of the Display BloX Widget and it is named as "Engagement Rate - % Change"
Values Panel containing the Value on the Display Widget
- Also includes a subtext to show the compared periods, which reads:
"Comparing <month 1> vs <month 2>"
This is auto-generated by the BloX Action code using the months selected on the dropdowns.
- Calculates the percentage change in the KPI between M1 and M2 using the formula below: the formula is based on calculating the % change in Engagement Rate, where
Step-by-Step Guide
1. Create the Input Widget
- Add a BloX widget to your dashboard.
- Use the attached BloX template - momCompareInput.json.
- Add the two date fields to the "Items" panel. Choose "Months" as the date granularity and Format as yyyy-MM. Set the title of the added date fields as "monthCompare" and "month"
Month Format of the Date Fields on the Input Widget
Update the three input parameters of the Action Block as described in the "How it works" section. (If you don't know the widgetId of the Display BloX Widget yet, come back and update that later)
2. Set Up the Custom BloX Action
- From One of the BloX widgets (as Custom Actions are common to all BloX Widgets in your Sisense instance), go to the "Action" tab on the Design panel. Use the three-dot to access the menu and choose "Create Action"
Creating a new BloX Action
- The name of the action should be "momRateCompare"(If you're using a different name for the action, then the Input Widget's Action block has to be updated accordingly).
- Use JavaScript (see above) to build the logic required.
- Click "Next" and then click "Create" on the subsequent screen.
3. Configure the Display Widget
- Add another BloX widget to display the result.
- For this, use the attached BloX template - momCompareDsply.json.
- In the "Values" panel of the BloX widget, create the formula that calculates the % change of the required KPI using measured value filters. Ensure you use both the date fields in your model, exactly how it was explained in the "How it works" section. The Value should have the title - "% Change" and the format should be set to "Percent". (If you're using a different name for the value, then the Display Widget's Editor has to be updated accordingly - Ln.35)
- Modify the displayed Title to what matches your KPI. In the Editor, modify Ln.26 to provide the new title.
- Note: Conditional Formatting has been used to display the % Change value in Red when the value is positive and Green when the value is negative. Do change the logic, if needed.
For reference, I have attached the dashboard that I built to demonstrate this usecase - MoMSelector-MeasuredValueFilter-BloX.dash.txt (download and rename it to MeasuredValueFilter-BloX.dash before importing). The dashboard will only work if you have the datasource - "MoM Compare BloX" (you can download the .sdata from the drive link and import it into your Sisense).
Conclusion
This use case demonstrates the power of BloX widgets in creating interactive and dynamic dashboards in Sisense. You can extend this use case to support other Date granularities like Years, Quarters, Weeks, and Days by simply tweaking the action code and BloX setup by a little bit. By enabling users to select and compare different periods, you can provide more flexible and insightful data analysis for any KPI.
Feel free to ask any questions or share your experiences with similar use cases in the comments below!
Happy BloXing!
Hi @gowtham_sisense
We are trying to build a KPI ndicator widget that will show the current value of KPI (based on user date filter) + also shows % change with 'previous period' which should be the dynamically adjusted based on user date values. for example
- Custom range
- single date (eg: Jan 17th) → Compares to the previous date (Jan 16th in this case)
- particular week (Jan 1 to Jan 17th) etc.. --> to compare with same period of time prior to the start of the range (the prior 17 days in this case)
- Current Week --> compared with the previous week
- Last Week → Compared to the week prior
- Current Month? → compared with last month
- Last Month → Compared to the month prior
- Last 3 Months --> compared with 3 months before last 3 months
- Last 6 Months --> compared with 6 months before last 6 months
- Last Year --> compared with previous year
Is this something that can be done in Sisense?
