Multiple Date Range Selectors using BloX for KPI Benchmark

[TriAnthony]

In certain scenarios, users may need to compare the same metric across two different time periods. Here are some example use cases:

• Sales velocity: Comparing average daily sales for the last 30 days vs. the last 90 days to identify if recent promotions are boosting sales.
• Customer acquisition: Comparing the number of new customers acquired in the last 90 days vs. the last 180 days to measure growth acceleration.
• Website or app traffic: Comparing page views or active users over the last 7 days vs. the last 30 days to see the impact of recent campaigns or updates.
• Inventory turnover: Comparing inventory sold in the last 30 days vs. the last 60 days to determine if demand is increasing or if there's overstock.

These time periods need to be dynamic and selectable, which means hard coding them as measured values (filter on formula) in the widgets will not work. One possible solution for this requirement is to duplicate the date field in the data model, and create two date filters (one for each time period) on the dashboard. The main drawback of this approach is that you will need two different widgets, one for each time period. Otherwise, the two filters will conflict/combine. The first date filter needs to be disabled in the second widget, and similarly, the second date filter needs to be disabled in the first widget.

A better solution is to use BloX to provide two date range selectors and apply the selections to the formula's measured value dynamically. This way, you can use a single widget to compare the metric across two different time periods. Here is an example:

Look below for a working dashboard example, along with the cube. Please note: our community website does not currently support .dash (dashboard) and .smodel (Elasticube) files. Please change the extensions of the files before importing them into your environment. Note that once imported, the cube still needs to be built.

Implementation

This BloX custom action allows users to select two date ranges and compare the KPI in those two periods, e.g. average daily revenue in the last 30 days vs. the last 90 days. The KPI doesn't necessarily have to be the same. For example, you can compare Revenue in the last 30 days vs Cost in the last 60 days. This custom action supports Pivot and Cartesian charts (column, bar, line, area).

Instructions:

1. Create the BloX widget.
   • Download and use the attached template (KPI_Benchmarker-2024-12-31.json).
   • Make changes as needed. The attached template includes 9 date range choices in the dropdown menu: 7 days, 15 days, 30 days, 60 days, 90 days, 120 days, 180 days, 360 days, 365 days. Add/remove the choices and make other changes as needed.
2. Add the custom BloX action.
   • Add the custom action at the end of this article to your environment: copy the entire code, create a new BloX custom action in your environment, and paste the code into the new action.
   • Name the action DateRangesSelectorForKPIComparison. If you choose to use a different name, you have to update the BloX code to reflect this change.
3. Create the widget(s) where you want compare the KPI.
   1. Add the first formula to the Values panel with a measured value on a date field set to the last any arbitrary number of days, e.g. (SUM(Revenue), Days in Date), where Days in Date is set to Last 30 days.
   2. Rename the KPI title to any text, but include "Last xx days" in the title, e.g. "Revenue - Last 30 days". The BloX action will automatically update the number in the title to reflect user selection.
   3. Add the second formula to the Values panel following the same instruction as step #3a, e.g. (SUM(Revenue), Days in Date), where Days in Date is set to Last 60 days.
   4. Rename the second KPI following the same instruction as step #3b, e.g. "Revenue - Last 60 days".
4. Add any arbitrary formula (it can also be a hard-coded value like 0) to the Values panel as a placeholder for the third formula, i.e. the delta calculation. The BloX action will automatically replace the formula with the delta calculation.
5. Create the next widget(s) as needed.
6. Add all widget IDs that you want to apply this custom action on in the BloX code under the widgetToModify parameter, then click Save.
7. On the BloX widget, select the first and second date ranges from the dropdown boxes, then hit Apply.

DateRangesSelectorForKPIComparison custom action:

//initialize variables
var widgetIds = payload.data.widgetToModify;
var filterValue1 = payload.data.selectVal1;
var filterValue2 = payload.data.selectVal2;

//loop through each of the specified widgets
payload.widget.dashboard.widgets.$$widgets
    .filter(i => widgetIds.includes(i.oid))
    .forEach(function (widget) {

        var newMeasure1Title = widget.metadata.panels[1].items[0].jaql.title.replace(/Last .*? days/, "Last " + filterValue1 + " days");
        var newMeasure2Title = widget.metadata.panels[1].items[1].jaql.title.replace(/Last .*? days/, "Last " + filterValue2 + " days");

        /***** Date Period 1 - Set date measured value for the first calculation *****/

        //check if the panel item contains context (i.e. a formula)
        if (widget.metadata.panels[1].items[0].jaql.context != undefined) {
            //get the JAQL context of the panel item
            var queryContext = widget.metadata.panels[1].items[0].jaql.context;

            //loop through each context in the item
            for (let [k, v] of Object.entries(queryContext)) {
                //find the context that contains the date measured value
                if (v.filter != undefined && v.datatype == 'datetime') {
                    //update the date measured value
                    v.filter.last.count = filterValue1;
                }
            }
        }

        //update the measure's title
        widget.metadata.panels[1].items[0].jaql.title = newMeasure1Title;

        //store the updated context and formula from the first calculation
        var formula1Context = widget.metadata.panels[1].items[0].jaql.context;
        var formula1Formula = widget.metadata.panels[1].items[0].jaql.formula;

        /***** Date Period 2 - Set date measured value for the second calculation *****/

        //check if the panel item contains context (i.e. a formula)
        if (widget.metadata.panels[1].items[1].jaql.context != undefined) {
            //get the JAQL context of the panel item
            var queryContext = widget.metadata.panels[1].items[1].jaql.context;

            //loop through each context in the item
            for (let [k, v] of Object.entries(queryContext)) {
                //find the context that contains the date measured value
                if (v.filter != undefined && v.datatype == 'datetime') {
                    //update the date measured value
                    v.filter.last.count = filterValue2;
                }
            }
        }

        //update the measure's title
        widget.metadata.panels[1].items[1].jaql.title = newMeasure2Title;

        //store the updated context and formula from the second calculation
        var formula2Context = widget.metadata.panels[1].items[1].jaql.context;
        var formula2Formula = widget.metadata.panels[1].items[1].jaql.formula;

        /***** Date Periods Difference - Set date measured values to the delta formula (difference between first and second formulas) *****/

        //get the JAQL
        var diffFormula = widget.metadata.panels[1].items[2].jaql;

        //delete the current context and formula
        delete diffFormula.context;
        delete diffFormula.formula;

        //re-add the contexts and formulas using the previously saved contexts and formulas from the first and second calculations
        diffFormula.context = Object.assign(formula1Context, formula2Context);
        diffFormula.formula = formula1Formula + ' - ' + formula2Formula;

        //apply and save changes to the widget
        widget.changesMade('plugin-BloX', ['metadata'])

        //refresh the widget
        widget.refresh();
    })