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 time periods. This solution allows users to select two months (or any two time periods) and dynamically calculates the percentage change in the KPI between these selected months.
Changing Measures In The Entire Dashboard Using Blox User Interface
Objective Display several related values (KPIs/Measures) in multiple widgets in the dashboard based on the user selection, in a simple and easy way, without duplicating widgets/formulas or tables in the Elasticube. As a direct result - the queries behind the widgets are extremely faster - lead to shorter loading time when the end-user needs to change the whole dashboard KPIs. Example files (works with Ecommerce Sample Elasticube): Dashboard Blox Template Blox Action Demonstration: Preparation Create a new Indicator widget and save its WidgetId. Create a custom action with this code below and name it SwitchMeasure var dimIndex = payload.data.selectVal - 1; var dimToSwapTo = payload.widget.metadata.panels[1].items[dimIndex].jaql; var widgetIds = payload.data.widgetToModify; payload.widget.dashboard.widgets.$$widgets .filter(i=>widgetIds.includes(i.oid)) .forEach(function(widget){ if(widget.metadata.panels[1].$$widget.type == 'indicator') { widget.metadata.panels[0].items[0].jaql = dimToSwapTo; } else { widget.metadata.panels[1].items[0].jaql = dimToSwapTo; } widget.changesMade(); widget.refresh(); }) Implementation 1. ADDING MEASURES TO SWITCH BETWEEN The formulas in the values panel define the measures your other widgets will switch between. Create a new Blox widget and add all the formulas (Up to 20 Formulas) you want to switch between into the values panel in the selection of the button Blox widget. Please pay attention to the order (top value to bottom -> left to right buttons) 2. CUSTOMIZING THE BUTTONS SELECTION WIDGET This step will define the values that are visible to the user in the button selection widget. -Add choices that reflect the ORDER and intuitive name the formulas in the values panel. -Add the widget IDs of the widgets you'd like to modify to the script of each option. You can find the widget ids in the URL when in widget editing mode. -Change the button color by editing the Background color for each option. For example, we will be switching between Revenue, Cost, and Quantity, our Blox script would be: { "style": "", "script": "", "title": "", "showCarousel": true, "titleStyle": [ { "display": "none" } ], "carouselAnimation": { "delay": 0, "showButtons": false }, "body": [ { "type": "TextBlock", "id": "", "class": "", "text": " " } ], "actions": [ { "type": "SwitchMeasure", "title": "Revenue", "style": { "backgroundColor": "#298C1A" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "1" } }, { "type": "SwitchMeasure", "title": "Cost", "style": { "backgroundColor": "#A31818" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "2" } }, { "type": "SwitchMeasure", "title": "Quantity", "style": { "backgroundColor": "#708AA5" }, "data": { "widgetToModify": [ "5f1b462e336d9c242cb9e8ea", "5f1b462e336d9c242cb9e8e5", "5f1b462e336d9c242cb9e8e7", "5f1b462e336d9c242cb9e8e9", "5f1b462e336d9c242cb9e8e8" ], "selectVal": "3" } } ] } 3. CUSTOMIZING THE BUTTON SELECTION ANIMATION Add the script (widget script) below to the indicator you created in the preparation section: Change the WidgetID to your indicator Widget ID Change the formula names based on the value list from the buttons selection widget Change the button’s titles based on the titles you selected the buttons selection widget //Widget Script: var ChooseYourUnselectColor = '#D3D3D3'; var Button1Color = '#298C1A'; var Button2Color = '#A31818'; var Button3Color = '#708AA5'; var widgetIndicator = '5f1b462e336d9c242cb9e8ea' widget.on('ready',function(widget, args){ var widgetOID = widgetIndicator; //Get the selected KPI object var widget = prism.activeDashboard.widgets.$$widgets.find(w => w.oid === widgetOID) if(widget.metadata.panels[0].items[0].jaql.title == 'Total Revenue'){ var textOfButtonToFormat1 = 'Cost'; var textOfButtonToFormat2 = 'Quantity'; var selectedButton = 'Revenue' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button1Color }); } else if (widget.metadata.panels[0].items[0].jaql.title == 'Total Cost') { var textOfButtonToFormat1 = 'Revenue'; var textOfButtonToFormat2 = 'Quantity'; var selectedButton = 'Cost' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button2Color }); } else { var textOfButtonToFormat1 = 'Revenue'; var textOfButtonToFormat2 = 'Cost'; var selectedButton = 'Quantity' $('button.btn:contains('+textOfButtonToFormat1+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+textOfButtonToFormat2+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": ChooseYourUnselectColor }); $('button.btn:contains('+selectedButton+')').css({ transition : 'background-color 50ms ease-in-out', "background-color": Button3Color }); } })Blox - Multi-Select Dropdown List Filter
Blox - Multi-Select Dropdown List Filter This article will take you step by step on how to create a multi-select dropdown filter using Blox and JavaScript. ElastiCube: 1. For each field you want to use in multi-select filter, you need to add a custom column. For instance, in our Sample ECommerce ElastiCube, add a custom column to the "Category" table: For Sisense on Windows add the below string in order to create the column: '<li><input type="checkbox" />'+[Category].[Category]+'</li>' For Sisense on Linux: '<li><input type=checkbox>'+[Category].[Category] + '</li>' 2. Change its name to [CategoryHTML]. 3. Do the same for the column [Country] from the table [Country] and name it [CountryHTML]. 3. Perform the 'Changes Only' build. Dashboard: 1. Download the dashboard attached and import it to your application. 2. Create a custom action in BloX and name it MultiBoxSelection: 3. Add the action's code below: var outputFilters = []; var widgetid = payload.widget.oid; var widgetElement = $('[widgetid="' + widgetid + '"]'); widgetElement.find($('blox input:checked')).parent().each(function () { var values = $('.fillmeup').attr('value') + $(this).text(); $('.fillmeup').attr('value', values); }).each((i, lmnt) => { outputFilters.push($(lmnt).text()); }) payload.widget.dashboard.filters.update( { 'jaql': { 'dim': payload.data.dim, 'title': payload.data.title, 'filter': { 'members': outputFilters }, 'datatype': 'text' } }, { 'save': true, 'refresh': true } ) 4. Action's snippet: { "type": "MultiBoxSelection", "title": "Apply", "data": { "dim": "FilterDimension", "title": "FilterTitle" } } 5. Add the widget's script. For each widget you need to change identifiers [CategoryList] and [CategoryItems] - these identifiers should be unique per each widget on a page: widget.on('ready', function() { var checkList = document.getElementById('CategoryList'); var items = document.getElementById('CategoryItems'); checkList.getElementsByClassName('anchor')[0].onclick = function(evt) { if (items.classList.contains('visible')) { items.classList.remove('visible'); items.style.display = "none"; } else { items.classList.add('visible'); items.style.display = "block"; } } items.onblur = function(evt) { items.classList.remove('visible'); } }); widget.on('processresult', function(a, b) { b.result.slice(1, b.result.length).forEach(function(i) { b.result[0][0].Text = b.result[0][0].Text + ' ' + i[0].Text }) }); These identifiers should be the same as you have in the widget in the value of [text]: { "type": "TextBlock", "spacing": "large", "id": "", "class": "", "text": "<div id='CategoryList' class='dropdown-check-list' tabindex='100'> <span class='anchor'>Select Category</span> <ul id='CategoryItems' class='items'>{panel:CategoryHTML}</ul> </div>" } 5. Click Apply on the widget and refresh the dashboard. Important Notes: Make sure you have the Category in the items (The new column was created) and name the Item "Category". Make sure you have a Category Filter (The actual category field and name it "Category") on the dashboard level. Make sure to replace the field and table names with the relevant field/table in the Action, in the editor of the Blox widget in the Blox items and in the dashboard filter. Disclaimer: Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.Quickly Add Unique Identifier to Blox Action Buttons Without Modifying Blox Template
A customer recently had the unusual request for a method to select and distinguish, via a CSS selector used within a custom Javascript action, for a specific Blox buttons in a existing Blox widget that contained multiple identical buttons, identical in inner text and all other parameters, without adding any new lines to the Blox template to add unique identifiers.Migrating Blox Custom Actions
When relocating Sisense resources from one server to another, the Sisense Rest API is a possible method for transferring Sisense components and objects like dashboards, users, groups, and datasources. Similarly, Blox Custom Actions can be migrated through Sisense Rest API endpoints.Sync Blox Input Element with Current Filter State
When creating a Blox widget altering a filter within a dashboard or widget, it is sometimes advantageous to modify the default behavior, that upon activating a Blox action , the Blox widget input or inputs retain the recently entered values instead of reverting to the default Blox inputs set in the Blox template. Additionally, when a user has the permission to directly modify the relevant filter, bypassing Blox widget actions, and subsequently makes changes to the filter that the Blox widget is designed to manipulate, the default Blox input values may no longer match with the current state of the filter.Multiple Date Range Selectors using BloX for KPI Benchmark
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: ALT text: A data visualization interface titled "Date Ranges Selector." It includes a date range selection option, with fields for "Date Range 1" and "Date Range 2," labeled 'Last 7 Days' and 'Last 30 Days.' Below, there is a pivot table displaying age ranges (0-18, 19-24, 25-34, 35-44, 45-54, 55-64, 65+) along with three columns: "Revenue Daily Average - Last 7 days," "Revenue Daily Average - Last 30 days," and "Difference." Two charts, a column chart on the left and a line chart on the right, represent the revenue averages for the specified age groups over the two date ranges. A working dashboard example, along with the cube can be found in the links below. Please note that once imported, the cube still needs to be built. Dashboard: BloX - Multiple Date Range Selectors for KPI Comparison Elasticube: Sample ECommerce - Current Dates 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: 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. 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. Create the widget(s) where you want compare the KPI. 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. 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. 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. Rename the second KPI following the same instruction as step #3b, e.g. "Revenue - Last 60 days". 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. Create the next widget(s) as needed. Add all widget IDs that you want to apply this custom action on in the BloX code under the widgetToModify parameter, then click Apply to save the widget. Test the BloX widget by selecting 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(); })BloX custom script which adjusts functionality to existing solution for date filter
You can refer to the following article: https://community.sisense.com/t5/knowledge/creating-from-to-date-using-blox/ta-p/771 which already does have a working solution on how to create a date filter with BloX widget, however 100% of customers are expecting after selecting dates within the BloX widget to stay selected, even If the page would be refreshed. This guide would explain and walk you through on how to accomplish such functionality.
