Input Parameters using BloX for What-If Analysis
Input Parameters using BloX for What-If Analysis This is an alternative to the paid or community Input Parameter plugins. This new BloX Input Parameter custom action: supports multiple levers (input parameters) with just one Apply button supports all widget types remembers previous selections even after refreshing/closing the dashboard loops through all widgets so dashboard designers do not have to specify widget IDs in the BloX code. Note: Be mindful of performance when considering this option as this will execute the BloX action on all widgets including those that the input parameters may not necessarily be relevant to. Input Parameter Implementation Instructions: Import the example dashboard attached below. Before importing the file, change the file extension from .txt to .dash. Make sure that you still have the Sample ECommerce Elasticube for this dashboard to properly display the data. Open the BloX Lever Configuration widget, then add the custom action given below to your Sisense environment. Go to the Actions tab, click the three-dot menu, then click Create Action. Copy the custom action code from below and paste it into your BloX custom action editor. Give this name for the action: ParameterSwap_V2_AllWidgets. You can also use a different name, but make sure the name of the action matches with the name referenced in the BloX code. Click Next, then Create. Click the Apply button to close the widget. Enter values to the levers and click Apply. The number on the widgets is now recalculated based on your input. There are three input parameters in this example. To add another input parameter: In the BloX code of the Lever Configuration widget, modify the number of input parameters in the BloX code under the paramsToModify parameter. For example, if you need 4 input parameters (levers), update the value to 4. On the left panel, add the additional swap_levers to the Values panel, i.e. swap_lever4, swap_lever5, etc. You can assign any arbitrary hard-coded number to these swap levers. To add the input box to the widget, copy one of the column elements then add it to the BloX code. Modify the id parameter to selectVal<lever number>, e.g. if this is the fourth lever, the id should be selectVal4. Similarly, modify the value parameter to match the swap_lever name you added in the previous step, e.g. [swap_lever4]. Update the title text as well and give it a meaningful description. Save the widget by clicking the Apply button. Add the additional levers to your widget formulas as needed so that they get recalculated when you enter the input values. Input Parameter BloX action: //initialize variables var swapParam = []; //create an array of swap_levers for (d = 0; d < payload.data.paramsToModify; d++) { swapParam[d] = "swap_lever" + (d + 1); } //loop through each of the specified widgets payload.widget.dashboard.widgets.$$widgets .forEach(function (widget) { //loop through each panel in the widget //exclude the filter panel (last panel) for (p = 0; p < widget.metadata.panels.length - 1; p++) { //loop through each item in the panel for (i = 0; i < widget.metadata.panels[p].items.length; i++) { //check if the panel item contains context (i.e. a formula) if (widget.metadata.panels[p].items[i].jaql.context != undefined) { var queryContext = widget.metadata.panels[p].items[i].jaql.context; //loop through each context in the item for (let [k, v] of Object.entries(queryContext)) { //loop through each swap_lever for (s = 0; s < swapParam.length; s++) { //check if the formula contains the swap_lever if (v.title == swapParam[s]) { var val = 'selectVal' + (s + 1); //update the formula with the swap_lever value that the user entered v.formula = payload.data[val]; } } } } } } //apply and save changes to the widget widget.changesMade('plugin-BloX', ['metadata']) //refresh the widget widget.refresh(); }) Resetting to Default Values The attached dashboard example includes a Reset button to reset the input parameters to pre-set default values. If you do not need this option, you can remove the second action from the BloX code, as shown in the highlighted part of the screenshot below. Instructions to implement the Reset button: Open the BloX Lever Configuration widget, then add the custom action given below to your Sisense environment. Go to the Actions tab, click the three-dot menu, then click Create Action. Copy the custom action code from below and paste it into your BloX custom action editor. Give this name for the action: ParameterSwap_V2_AllWidgets_Reset. You can also use a different name, but make sure the name of the action matches with the name referenced in the BloX code. Click Next, then Create. In the BloX code, update the defaultValues array with your required default values. See screenshot below for reference. There are three input parameters in this example, if you have more/less parameters, update the paramsToModify value to the correct number of parameters that you have. See screenshot below for reference. Click the Apply button to close the widget. Reset Input Parameter BloX action: //initialize variables var leverValues = payload.data.defaultValues; var swapParam = []; //create an array of swap_levers for (d = 0; d < payload.data.paramsToModify; d++) { swapParam[d] = "swap_lever" + (d + 1); } //loop through all widgets in the dashboard payload.widget.dashboard.widgets.$$widgets .forEach(function (widget) { //loop through each panel in the widget //exclude the filter panel (last panel) for (p = 0; p < widget.metadata.panels.length - 1; p++) { //loop through each item in the panel for (i = 0; i < widget.metadata.panels[p].items.length; i++) { //check if the panel item contains context (i.e. a formula) if (widget.metadata.panels[p].items[i].jaql.context != undefined) { var queryContext = widget.metadata.panels[p].items[i].jaql.context; //loop through each context in the item for (let [k, v] of Object.entries(queryContext)) { //loop through each swap_lever for (s = 0; s < swapParam.length; s++) { //check if the formula contains the swap_lever if (v.title == swapParam[s]) { var val = 'selectVal' + (s + 1); //update the formula with the default value v.formula = leverValues[s]; } } } } } } //apply and save changes to the widget widget.changesMade('plugin-BloX', ['metadata']) //refresh the widget widget.refresh(); }) We hope this is a helpful article and would love to hear about your experience in the comments!
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.
Reset to Default Filters Button using BloX
This article will explain how to create reset to default filters button in BloX This helps in case you want to provide this icon functionality on the dashboard and not just in the Filters panel. Create a new custom action with the below code: *Make sure you're naming the action 'Click' if you're using the attached dashfile in section 2 var dashboard = payload.widget.dashboard dashboard.filters.update(dashboard.defaultFilters,{refresh:true}) You can use the dashfile attached (Backtodefaultfilters.dash ) OR Copy & Paste the below JSON code in the Editor: { "style": "", "script": "", "title": "", "showCarousel": true, "body": [], "actions": [ { "type": "Click", "title": "Reset Filters" } ] }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.Filter BETWEEN two dates in the model
Filter BETWEEN two dates in the model This article explains how to create a filter to return data between two fields in the model. The following cases will be covered: Show only active contracts on the given date; To show the status of the record on the given date. The first challenge is to show only active contracts on the given date. Let’s assume that we have the following columns in the table “History”: Id; From; To. Let's assume that the contract is active if the given date is between [From] and [To]. To filter the dates we will create two filters on the dashboard using these fields. Now, end-users can choose the same date in these filters. This is not a good user experience, because the user will have to update two filters. To improve user experience we will do the following: Lock these filters - a user will not update these filters manually. Use element Input.Date of the widget “BloX” to let a user provide a date; Create a custom action to update the filters [From] and [To]. Let’s create the action “betweenDates”: const { data, widget } = payload; const { selectedDate, fromDateDim, toDateDim } = data; if (!(selectedDate && fromDateDim && toDateDim)) { return; } const fromFilter = widget.dashboard.filters.item(true, fromDateDim); const toFilter = widget.dashboard.filters.item(true, toDateDim); if (!(toFilter && fromFilter)) { return; } setNewDatesInFilters(fromFilter, toFilter, selectedDate); widget.dashboard.refresh(); function setNewDatesInFilters(fromFilter, toFilter, newDate) { toFilter.jaql.filter = { from: newDate }; fromFilter.jaql.filter = { to: newDate }; updateFilter(toFilter.jaql); updateFilter(fromFilter.jaql, true); } function updateFilter(filterObject, save = false) { widget.dashboard.filters.update({ jaql: filterObject }, { save, refresh: false }) } Snippet of this action: { "type": "betweenDates", "title": "Apply", "data": { "selectedDate": "", "fromDateDim": "", "toDateDim": "" } } Let’s use this action in the BloX. Also, we will add the element “Input.Date”. As the result, we will get the following BloX JSON: { "style": "", "script": "", "title": "", "showCarousel": true, "body": [ { "type": "Container", "items": [ { "type": "Input.Date", "id": "data.selectedDate", "class": "" } ] } ], "actions": [ { "type": "betweenDates", "title": "Apply", "data": { "selectedDate": "", "fromDateDim": "[History.From (Calendar)]", "toDateDim": "[History.To (Calendar)]" } } ] } Now, you can choose a date and after clicking “Apply” both filters will be updated with the selected date. The only issue - input field is cleared. We will fix this by adding a simple widget’s script to the widget BloX - this script will persist the selected date in the input field: widget.on('ready', () => { const toFilter = widget.dashboard.filters.item('[History.To (Calendar)]'); const selectedDate = $$get(toFilter, "jaql.filter.from"); if (selectedDate && document.getElementById('data.selectedDate')) { document.getElementById('data.selectedDate').value = selectedDate; } }) Now, when the dashboard is loaded, the script finds the previously selected date and fills in the input field. Also, when a user changes the date, the newly input date is shown. Let’s solve the second challenge to show statuses of the record on the given date. Let’s assume that we have table “Cases”. In this table we have historical information about cases: Id - unique identifier of the record; CaseId - case’s identifier; Status - status of the case; Date - date when case status was changed. Sample of the data in this table: [ALT text: A table displaying case information with the following columns: Id, CaseId, Status, and Date. The table includes rows showing various case entries with status updates such as "New," "Open," "Reopen," and "Closed," along with corresponding timestamps for each case.] Use-case: as an end-user, I want to input the date and Sisense should return the status of the case in the given date. To accomplish this, let’s create a new custom table “History”, where we will compute the first date of the status and its last date. This custom table will be populated with the next SQL query: SELECT CaseId, [Old Status], [New Status], [From], [To] FROM ( SELECT c.[CaseId], c.[Status] "Old status", cc.[Status] "New status", c.[Date] "From", cc.[Date] "To", RankCompetitionAsc(c.CaseId, c.[Status], c.Date, cc.Date) "Rank" FROM Cases c INNER JOIN Cases cc ON c.[CaseId] = cc.[CaseId] and c.date < cc.date ) c WHERE c.Rank = 1 UNION ( SELECT c.[CaseId], c.[Status] "Old Status", '' "New status", c.date "From", CreateDate(2999, 12, 31) "To" FROM Cases c INNER JOIN ( select caseId, max(date) "MaxDate" from cases group by caseId ) latestStatus ON c.[CaseId] = latestStatus.caseId and latestStatus.MaxDate = c.date ) This query contains two parts: The first part returns statuses, which were already changed and we have information about the status after the change; The second part shows the current status of the record. As a result of the data transformation, we will have the following structure: [ALT Text: A table displaying status updates with columns labeled "CaseId," "Old Status," "New Status," "From," and "To." There are five rows of data showing various cases, their old and new statuses, and corresponding timestamps for when the status changes occurred. The "From" and "To" columns indicate the date and time of the changes. Some cases are marked as "New," "Open," "Closed," and "Reopen."] Now, we will use approach from the first part of this article to create dashboard’s filters and utilize BloX to input the date and custom action to update the dashboard’s filters. Conclusion In this article we reviewed a possible way to manipulate date filters. A similar approach you can use for embedding cases. Since the filters will be controlled from the BloX widget and filters will be locked for manual editing, you can even hide these dates filters using the following plugin: https://www.sisense.com/marketplace/add-on/hide-filters Similar Content: Sisense Docs Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this please let us know.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 }); } })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: https://drive.google.com/file/d/15e38-FV5He9-7gi_JUPyHkg10I0_aFaH/view?usp=drive_link Elasticube: https://drive.google.com/file/d/1ZWMqPSeZOseAoSu4CYqhbeay7b1IKki9/view?usp=drive_link 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(); })
How to Create BloX Custom 'From-To' Filter -- All Dates with “Clear” Button
How to Create BloX Custom 'From-To' Filter -- All Dates with “Clear” Button We will create a Blox widget to have date filters with the 'From and To' fields and the button Clear. Actually, this button will add a whole range of dates to the filter. Create a new BloX widget and copy-paste the next code to the Editor window: { "style": "", "script": "", "title": "", "showCarousel": true, "carouselAnimation": { "showButtons": false }, "body": [ { "type": "Container", "width": "20%", "style": { "margin": "0 auto" }, "items": [ { "spacing": "large", "type": "TextBlock", "text": "From Date", "weight": "light", "color": "black" }, { "type": "Input.Date", "id": "SelectVal_from", "placeholder": "", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "spacing": "medium", "type": "TextBlock", "text": "To Date", "color": "black" }, { "type": "Input.Date", "id": "SelectVal_to", "placeholder": "", "defaultValue": "", "style": { "width": "100%" }, "borderRadius": "4px", "borderStyle": "none", "backgroundColor": "#F4F4F8" }, { "type": "ActionSet", "actions": [ { "type": "filter-date", "title": "Submit", "data": { "FilterFields": [ "[Commerce.Date (Calendar)]" ] } }, { "type": "filter-datecls", "title": "Clear", "data": { "FilterFields": [ "[Commerce.Date (Calendar)]" ] } } ] } ] } ] } Next code please copy to the Configuration tab { "fontFamily": "Open Sans", "fontSizes": { "default": 16, "small": 14, "medium": 22, "large": 32, "extraLarge": 50 }, "fontWeights": { "default": 400, "light": 100, "bold": 800 }, "containerStyles": { "default": { "backgroundColor": "#ffffff", "foregroundColors": { "default": { "normal": "#3A4356" }, "white": { "normal": "#ffffff" }, "grey": { "normal": "#9EA2AB" }, "orange": { "normal": "#f2B900" }, "yellow": { "normal": "#ffcb05" }, "black": { "normal": "#000000" }, "lightGreen": { "normal": "#93c0c0" }, "green": { "normal": "#2BCC7F" }, "red": { "normal": "#FA5656" }, "accent": { "normal": "#2E89FC" }, "good": { "normal": "#54a254" }, "warning": { "normal": "#e69500" }, "attention": { "normal": "#cc3300" } } } }, "imageSizes": { "default": 40, "small": 40, "medium": 80, "large": 120 }, "imageSet": { "imageSize": "medium", "maxImageHeight": 100 }, "actions": { "color": "", "backgroundColor": "", "maxActions": 5, "spacing": "large", "buttonSpacing": 20, "actionsOrientation": "horizontal", "actionAlignment": "right", "margin": "0", "showCard": { "actionMode": "inline", "inlineTopMargin": 16, "style": "default" } }, "spacing": { "default": 5, "small": 5, "medium": 10, "large": 20, "extraLarge": 40, "padding": 0 }, "separator": { "lineThickness": 1, "lineColor": "#D8D8D8" }, "factSet": { "title": { "size": "default", "color": "default", "weight": "bold", "warp": true }, "value": { "size": "default", "color": "default", "weight": "default", "warp": true }, "spacing": 20 }, "supportsInteractivity": true, "imageBaseUrl": "", "height": 197 } Now we need to add 2 Actions - 'filter-date' (for the filter) and 'filter-datecls' (for “clear” button). So, go to the Actions tab and create the first Action with name - filter-date const filVal_from = payload.data.SelectVal_from const filVal_to = payload.data.SelectVal_to const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; newFilter = { jaql: { dim: "", filter: { from: filVal_from, to: filVal_to } } }; filterDims.forEach(function (dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }) }) After this press Next and Create buttons. And now we will add the second Action with name - 'filter-datecls' const filVal_from = "1980-03-04T00:00:00" const filVal_to = "2063-03-04T00:00:00" const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; newFilter = { jaql: { dim: "", filter: { from: filVal_from, to: filVal_to } } }; filterDims.forEach(function (dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }) }) After this press Next and Create buttons. Here we found how to create a nice-looking BloX widget with dates filters and a "clear" button. You can add some new options that will better fit your needs. ALT text: A user interface displaying a data management tool. On the left side, there is an area labeled "Add Rows" with entries from different years (2000 to 2022). On the right side, there are options for selecting "Single" or "Multiple" items, along with a button to "Submit" or "Clear." The overall layout is organized and features dropdown menus and buttons for user interaction.BloX: replicating action “send me the report now”
BloX: replicating action “send me the report now” This article explains how to develop an action to send a dashboard as a report to the end user. This action replicates the action “Send me the report now”. This action is available only to the dashboard’s owner, but we will develop a BloX action, which will be available for other users. To solve this challenge you will need to do the following: Create a widget of the type ‘BloX’ on the dashboard you want to have the ability to send reports to the end-users; Create a custom action with the following code: const { widget } = payload; //Get widget’s object from the payload const internalHttp = prism.$injector.get('base.factories.internalHttp'); //Get internal factory to run API requests internalHttp({ url: '/api/v1/reporting', method: 'POST', contentType: 'application/json', data: JSON.stringify({ assetId: widget.dashboard.oid, assetType: "dashboard", recipients: [ { type: 'user', recipient: prism.user._id } ], preferences: { inline: true } }) }).then(res => console.log(res.data)); This action will have the following snippet: { "type": "sendMeReport", "title": "Send me report" } Use this snippet in the widget you have created: { "style": "", "script": "", "title": "", "showCarousel": true, "body": [], "actions": [ { "type": "sendMeReport", "title": "Send me report" } ] } Now, you have a button on the widget. After clicking this button, Sisense will send a report for the currently authenticated user. Please, note that there will be no indication of the running action. When the action is completed there will be a message in the browser’s console. Feel free to customize the logic of this action to show the end-user that the report is generating. Custom action is a powerful tool, which allows you to create custom interactions. Use this approach to create a new action easily. You can customize the proposed action by adding an indication of the running action or by using custom parameters to change format or size of the generated report (check the description of the endpoint /api/v1/reporting for additional information). Check out related content: Creating customer actions Reporting Send Reports
