Custom Filtering to allow users to filter for specific zip codes
Hi DRay and friends, I am trying to allow my users to filter for multiple different zip codes, and specific zip codes to their use cases. Right now in Sisense a user would have to search and click the ones they want, I know with custom code filtering then can give the codes they want to filter for. Is there any easier way, maybe BloX, where a user can search/filter for multiple zip codes without looking them up/without using the custom code filter option? example: { "explicit": true, "multiSelection": true, "members": [ 33825, 34420, 33834, 33525, 34432, 32134, 33843, 34690, 34691, 33852, 33810, 34638, 34639, 33549, 33559, 33558, 34652, 34470, 34471, 34472, 34473, 34474, 34475, 34476, 34479, 34480, 34481, 34482, 33556, 32179, 34683, 34684, 33576, 33870, 33872, 33875, 33584, 34488, 34491, 33604, 33605, 33610, 33612, 33613, 33614, 33617, 33618, 33619, 33624, 33625, 33637, 33647, 33603, 33607, 33609, 33615, 33626, 33634, 33635, 34689, 33592, 33873, 33543, 33545, 33544, 33540, 33541, 33542, 33823, 33827, 33830, 34714, 33523, 33837, 33896, 33897, 33838, 33839, 33841, 33844, 33847, 33855, 33849, 34759, 33850, 33851, 33853, 33859, 33898, 33801, 33803, 33805, 33809, 33811, 33812, 33813, 33815, 33854, 33860, 33856, 33868, 33867, 33877, 34787, 33880, 33881, 33884 ] }
Add presets to a Blox date filter widget
We have some dashboards that have widgets as filters. One of these is a Blox widget that functions as a date filter, which I created with help from the community here. I recently added presets to the date filter to make it easier and faster to apply date filtering. Create a Blox Widget Paste the script below in the script editor section. { "style": ".blox-slides button:hover{background-color:#014E66 !important;} .date-input-container { position: relative; } .date-input-container input[type='date'] { cursor: pointer; } .date-input-container::before { content: ''; position: absolute; top: 0; left: 0; right: 0; bottom: 0; z-index: 1; cursor: pointer; } .date-input-container input::-webkit-calendar-picker-indicator { opacity: 0; position: absolute; right: 10px; width: 20px; height: 20px; cursor: pointer; z-index: 2; }", "title": "", "showCarousel": true, "carouselAnimation": { "showButtons": false }, "script": "setTimeout(function() { const fromInput = document.getElementById('SelectVal_from'); const toInput = document.getElementById('SelectVal_to'); function formatDate(date) { const year = date.getFullYear(); const month = String(date.getMonth() + 1).padStart(2, '0'); const day = String(date.getDate()).padStart(2, '0'); return year + '-' + month + '-' + day; } function setDates(fromDate, toDate) { if (fromInput) fromInput.value = formatDate(fromDate); if (toInput) toInput.value = formatDate(toDate); } function getDateRanges() { const today = new Date(); const currentYear = today.getFullYear(); const currentMonth = today.getMonth(); const currentQuarter = Math.floor(currentMonth / 3); return { last30days: { from: new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000), to: today }, quarter: { from: new Date(currentYear, currentQuarter * 3, 1), to: new Date(currentYear, (currentQuarter + 1) * 3, 0) }, ytd: { from: new Date(currentYear, 0, 1), to: today }, lastyear: { from: new Date(currentYear - 1, 0, 1), to: new Date(currentYear - 1, 11, 31) } }; } const ranges = getDateRanges(); document.querySelectorAll('[data-filter-type]').forEach(function(btn) { btn.addEventListener('click', function() { const filterType = this.getAttribute('data-filter-type'); if (ranges[filterType]) { setDates(ranges[filterType].from, ranges[filterType].to); } }); }); if (fromInput) { fromInput.addEventListener('click', function(e) { if (e.target.tagName === 'INPUT') { e.target.showPicker ? e.target.showPicker() : e.target.click(); } }); fromInput.style.cursor = 'pointer'; } if (toInput) { toInput.addEventListener('click', function(e) { if (e.target.tagName === 'INPUT') { e.target.showPicker ? e.target.showPicker() : e.target.click(); } }); toInput.style.cursor = 'pointer'; } }, 1000);", "body": [ { "type": "Container", "width": "90%", "style": { "margin": "0 auto" }, "items": [ { "type": "ActionSet", "actions": [ { "type": "date-preset", "title": "Last 30 Days", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "last30days", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "This Quarter", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "quarter", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "Year to Date", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "ytd", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "date-preset", "title": "Last Year", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterType": "lastyear", "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } } ] }, { "type": "Container", "style": { "display": "flex", "flexDirection": "row", "justifyContent": "space-between", "marginTop": "20px", "gap": "10px" }, "items": [ { "type": "Container", "style": { "width": "48%" }, "items": [ { "type": "TextBlock", "text": "From", "weight": "lighter", "color": "black" }, { "type": "Container", "style": { "position": "relative" }, "items": [ { "type": "Input.Date", "id": "SelectVal_from", "placeholder": "mm/dd/yyyy", "calendar": true, "style": { "width": "100%", "padding": "14px", "background-color": "#F4F4F8", "border-radius": "8px", "border": "1px solid #ccc", "font-size": "16px", "cursor": "pointer" } } ] } ] }, { "type": "Container", "style": { "width": "48%" }, "items": [ { "type": "TextBlock", "text": "To", "weight": "lighter", "color": "black" }, { "type": "Container", "style": { "position": "relative" }, "items": [ { "type": "Input.Date", "id": "SelectVal_to", "placeholder": "mm/dd/yyyy", "calendar": true, "style": { "width": "100%", "padding": "14px", "background-color": "#F4F4F8", "border-radius": "8px", "border": "1px solid #ccc", "font-size": "16px", "cursor": "pointer" } } ] } ] } ] }, { "type": "ActionSet", "style": { "marginTop": "20px", "text-align": "center" }, "actions": [ { "type": "DateX", "id": "submit_btn", "title": "Apply", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } }, { "type": "filter-date-clear", "title": "Clear", "style": { "color": "white", "background-color": "#007FAA" }, "data": { "FilterFields": [ "[Dm_dates.date_data (Calendar)]" ] } } ] } ] } ] } Create the necessary actions for the buttons to work: date-preset const filterType = payload.data.FilterType; const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; const now = new Date(); const yyyy = now.getFullYear(); const mm = String(now.getMonth() + 1).padStart(2, '0'); const dd = String(now.getDate()).padStart(2, '0'); const today = `${yyyy}-${mm}-${dd}`; let fromDate = ''; let toDate = today; //Year to date if (filterType === 'ytd') { fromDate = `${yyyy}-01-01`; //Quarter } else if (filterType === 'quarter') { const q = Math.floor(now.getMonth() / 3); const startMonth = q * 3 + 1; fromDate = `${yyyy}-${String(startMonth).padStart(2, '0')}-01`; //Last Year } else if (filterType === 'lastyear') { fromDate = `${yyyy - 1}-01-01`; toDate = `${yyyy - 1}-12-31`; // Last 30 days: from 30 days ago to today } else if (filterType === 'last30days') { const pastDate = new Date(now); pastDate.setDate(pastDate.getDate() - 30); const pastY = pastDate.getFullYear(); const pastM = String(pastDate.getMonth() + 1).padStart(2, '0'); const pastD = String(pastDate.getDate()).padStart(2, '0'); fromDate = `${pastY}-${pastM}-${pastD}`; } else { console.log('Unknown FilterType:', filterType); if (typeof sendResponse === 'function') sendResponse(false); return; } let newFilter = {}; $('#SelectVal_from').val(fromDate); $('#SelectVal_to').val(toDate); newFilter = { jaql: { dim: "", filter: { from: fromDate, to: toDate } } }; filterDims.forEach(function(dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }); }); Datex -- Apply button var today = new Date(); var dd = String(today.getDate()).padStart(2, '0'); var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0! var yyyy = today.getFullYear(); today = yyyy + '-' + mm + '-' + dd; const filVal_from = payload.data.SelectVal_from == '' ? '1800-01-01' : payload.data.SelectVal_from; const filVal_to = payload.data.SelectVal_to == '' ? '2100-01-01' : payload.data.SelectVal_to; const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; console.log(filVal_from); console.log(filVal_to); 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 }) }) Clear dates var today = new Date(); var dd = String(today.getDate()).padStart(2, '0'); var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0! var yyyy = today.getFullYear(); today = yyyy + '-' + mm + '-' + dd; const filVal_from = "1800-01-01" const filVal_to = '2100-01-01' const filterDims = payload.data.FilterFields; const dash = payload.widget.dashboard; let newFilter = {}; newFilter = { jaql: { dim: "", filter: { from: filVal_from, to: filVal_to } } }; $('#SelectVal_from').val(''); $('#SelectVal_to').val(''); filterDims.forEach(function (dim) { newFilter.jaql.dim = dim; dash.filters.update(newFilter, { refresh: true, save: true }) }) Save everything and try it out.Dynamically Updating Widget Titles Based on Filter Selections in Sisense (Linux)
Dynamically Updating Widget Titles Based on Filter Selections in Sisense (Linux) Introduction This article guides how to dynamically change the title of a Sisense widget based on the filter selection. While this is an advanced solution and not natively supported by Sisense functionality, a custom script can be used to achieve this effect. Please proceed with caution, as this is a custom implementation. [ALT Text: A dashboard display titled "Europe, USA (by Region)" showing a gauge indicating an "Actual Margin" of 0.22. The gauge ranges from -0.25 to 0.25. A sidebar lists regions: Europe, N/A, and USA, with checkboxes next to each.] Step-by-Step Guide Navigate to the desired dashboard in Sisense and select the widget you want to change the title dynamically. Open the widget's settings and access the script editor under the 3 dots menu > Edit Script. Copy and paste the following code into the widget's script editor: widget.on("render", function () { let title = "by Region"; // Set a default title // Find the specific filter by its column name let filterUsed = dashboard.filters.$$items.find( (filter) => filter.jaql.column === "Region" ); // Determine the filter text or set default let filterText = filterUsed && filterUsed.jaql.filter.members?.length ? filterUsed.jaql.filter.members.join(", ") : "All Regions"; // Flag to prevent multiple updates let isModified = false; if (!isModified) { widget.title = filterText + ' ' + title; isModified = true; } }); Adjust the filter.jaql.column value instead of "Region" in the script to match the actual filter you intend to use. Update the default title instead of "by Region". Modify the "All Regions" value to be displayed for the “Include All” filter. Save the changes in the script editor. Apply different filter selections on your dashboard to verify that the widget title updates according to the chosen filters. Troubleshooting and Limitations: Ensure that the filter column specified in the script matches an existing filter on the dashboard. The script works only for filters of include type. For excluding filters the additional logic should be implemented. Conclusion By following the steps outlined above, you can set up your Sisense widget titles to dynamically update based on filter selections. While this approach offers flexibility, it involves a custom script and requires careful handling, as modifications made this way are outside Sisense's standard support. 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.Passing Filters via URL Parameters for Dashboards with Separate Datasources
Sisense includes a native included feature and format for passing URL filters via URL parameters, as documented here. By default, this functionality copies filters in full, including the datasource parameter of the filter, and includes every filter automatically. It results in very long URL's, and includes many parameters that are not always required, as the full filter object is included. Previous Knowledge Base articles articles have discussed how similar behavior can be recreated customized via scripting for more flexible usage. However, those approaches applied only to member-type filters, excluding other filter types and multi-level dependent filters. The code shared below demonstrates a more flexible filter modification via URL modification approach. It includes both creating URL parameters and reading URL parameters for filter modification, whether this code is in a script or plugin. This method applies to all filter types and can be used to transfer filters between dashboards using different datasources. This code works in both dashboard and widget scripts as well as plugins. If your datasources use different dimension names, this code can be adopted to map and match the aligned dimensions.How to Visually Differentiate Active Filters from "Include All" [Linux]
This article addresses how to visually differentiate active filters from "Include All" filters in Sisense dashboards. Many users find it challenging to distinguish between active filters (filters with one or more values selected) and inactive ones that include all options. This guide provides a solution for making active filters more visually prominent, which is particularly beneficial when the dashboard supports multiple languages and thus cannot rely solely on text changes.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.How to Troubleshoot UI Issues Using DevTools (HAR File & Console Logs)
If a webpage, dashboard, or widget isn't loading properly, encounters errors during exporting, importing, editing, or opening, or if buttons are unresponsive and error messages appear, you can use Developer Tools (DevTools) in your browser to diagnose the issue. This guide will show you how to: - Check the Network tab for failed requests. - Use the Preview and Response tabs to see details of errors. - Check the Console tab for other issues. - Save a HAR file to share with support.Comparative analysis using the filtered measure plugin
Comparative analysis using the filtered measure plugin By default, filter selections in Sisense are applied at the dashboard or widget level. The Filtered Measure add-on enables the creation of additional selection states, allowing you to display two or more different sets of selections within a single widget. For more details, please refer to the link below. Here are some common use cases for Filtered Measures: Comparative Analysis Example: Compare the same metric (e.g., Revenue) across two different date ranges (e.g., this month vs. last month) to analyze trends or identify growth patterns. Filter Applied Directly on a Measure Instead of applying a filter to the entire widget or dashboard, apply the filter directly to a specific measure (e.g., applying a filter to "Sales" based on a particular product category, without affecting other parts of the dashboard). This allows for more granular control over the displayed data while preserving other elements of the analysis. This article highlights a comparative analysis of a single measure, Revenue, across multiple dimensions—Date and Category. It incorporates two filters for each dimension: Primary and Compare. Background When it comes to comparative analysis, dashboards are often designed to display measures broken down by the dimensions being compared. This approach can make it challenging to extract specific values and may require adding additional dimensions for grouping. Example Dashboard Alt text: A dashboard displaying total revenue information. At the top, there is a summary showing total revenue of $263,661, with a breakdown indicating that 61% comes from Europe and 39% from the USA. Below, bar graphs illustrate total revenue by category, listing categories such as Bikes, Body Armor, Cables & Hubs, and more, with respective revenue figures. At the bottom, a line graph represents total revenue over several months, showing fluctuations with values ranging from $1.6K to $29.3K. This dashboard currently does not address the following questions: How can we filter by Category and compare Bikes to Wheels & Wheelsets across different Regions? How can we evaluate the performance of the Category Bikes against Wheels & Wheelsets over time? How can we compare a specific group of members within a Category to the overall total of the Category? How can we compare specific date ranges, such as Yesterday versus the same day last week? How can we provide flexible date selection options tailored to the user’s preferences? Filtered Measure - Implementation Steps Enable the Measured Filter Plugin Ensure the Measured Filter Plugin is activated in your environment. Modify the Data Model - Navigate to the Data Model. - Locate the Dimension table that requires a comparison - Duplicate the column you want to compare (e.g., the Date column) and assign it a meaningful name, such as Date_compare - Build or Publish the data model after applying the changes Prepare the Dashboard - Create a new dashboard or update an existing one. - Add both filters - Primary and Compare - as Dashboard Filters - Maintain a clear naming convention for the filters (e.g., Date for the primary filter and Date_compare for the comparison filter). Update Widgets with Measure Filters - Create a new widget or update an existing one - Add the relevant measure filters (Primary and Compare) to the metric. For example: ALT text: "Screenshot of a formula editor displaying a formula that sums order revenue based on category name and years in date." Rename Filter Measures - Rename the filter measures by adding an @ symbol before the name. - Update the formula syntax to include the renamed filters. Example ALT Text: A screenshot of a formula editor displaying a calculation formula in a spreadsheet application. The formula is a SUM function that includes parameters for "Order Revenue," "Category Name," and "Date." The text is shown in a code-like format, with certain elements in blue. - Ensure that the filter name in the dashboard matches the Measure Filter name used in the formula. - Repeat the Same for the Compared measure ALT text: A screen displaying a formula editor in a data visualization tool. The formula shows a calculation using the SUM function on "OrderRevenue," referencing "CategoryName_Compare" and "Date_Compare." The interface includes sections labeled "Values" and "Break by." Key Points of the Filtered Measure Plugin The @ symbol is required in the formula to apply the dashboard filter as a parameter. However, it should not be added to the name of the dashboard filter itself. Grouping by the Compared Dimensions is unnecessary and adds no value. Filtered measures support multiple measures and dimensions In the same formula, it is possible to include measures for both the Primary measure and the Compare measure. For example: ALT Text: A screenshot displaying a data analysis tool. The left side shows a panel for selecting categories labeled "Region" and "Values," while the lower section features a formula editor with a mathematical expression for calculating revenue compare by region. For Indicator, Blox widgets, and any widget using a single measure applied to either Primary or Compare, disable the corresponding filter. Similarly, turn off the other filter when switching between them. ALT Text: A screenshot of a data visualization interface showing "Revenue Primary" as the main heading. Below it, there are options labeled "Date Compare" and "Category Level Compare" with a highlighted checkbox next to each option. The current displayed revenue amount is $95,751, prominently positioned in the center of the screen. The interface includes buttons labeled "Apply" and "Cancel" at the top. If both measures, Primary and Compare, are used, ensure that both filters are enabled. Here are some common examples of comparative analysis: Use Case 1 - Compare Category Dimension ALT Text: A dashboard displaying revenue statistics. At the top, two sections show “Revenue Primary” and “Revenue Compare,” both with a figure of $76,274. Below, a third section displays “Revenue Compare Over Time” with a line graph illustrating revenue trends, peaking around $62,597. The bottom section features a table listing revenue by employee, with names and corresponding revenue figures, including entries for Andrew Williams and Emily Johnson. The visualizations include bar charts, line graphs, and a detailed employee revenue table. This example compares between Category Bikes and Wheels & Wheelsets over 3 groups by dimensions: Displaying the Revenue asan Indicator for each one of the Categories. Compare the Revenue over time ALT Text: A line graph titled "Revenue Compare Over Time" showing two data series. The blue line represents "Revenue," while the orange line indicates "Revenue Compare." The vertical axis measures revenue in dollars, ranging from $0 to $30,000. The horizontal axis displays dates from May 2011 to March 2012. The graph illustrates fluctuations in both revenue metrics over the specified time period with markers indicating values at various points. Compare the Revenue by Region Summary Table of Revenue Comparison by Employee Use Case 2 - Compare multiple Dimensions: Date and Category ALT Text: A data visualization dashboard displaying revenue metrics. The top section shows total revenues for "Revenue Primary" ($33,832) and "Revenue Compare" ($34,743). Below, a bar chart compares revenue by region, with separate bars for Europe and USA. Another chart shows revenue comparison by employee, with individual bars for names including Aria Williams, John Doe, Emily Johnson, and others. The dashboard features filters on the right side for data customization. This example compares Dates and Categories. The implementation of the measured filter is the same as shared above: (sum([Order Revenue]),[@CategoryName],[@Date]) Enable dynamic comparison between different time ranges from the viewer's side, rather than being predefined by the dashboard designer. Compare Category and Date over time Use Case 3 - Compare Different Date Ranges with Different granularities Compare Year to Single Quarter Example ALT Text: A data visualization showing revenue comparisons across different territories and employees. The top section displays a horizontal bar chart comparing revenue by TerritoryID, with revenue values in blue bars and revenue compare values in orange. The lower section features a bar chart comparing revenue by employee, with blue bars representing individual revenues and orange bars indicating revenue comparisons. Key figures are labeled, with total revenue values ranging from $380 to $16K. Date selection options are visible in the upper right corner, indicating the year 2012 and the fourth quarter of 2011. Compare Different Custom Date Ranges Example ALT Text: A data visualization dashboard displaying revenue comparisons. The upper section shows a horizontal bar chart labeled "Revenue Compare By TerritoryID" with blue bars indicating revenue amounts and orange bars representing revenue comparisons for various Territory IDs. The lower section features a vertical bar chart titled "Revenue Compare By Employee," highlighting revenue data for individual employees. Each bar is labeled with revenue amounts, with blue bars indicating revenue and a few orange bars for comparison. The sidebar includes date-selectors for filtering the data. Use Case 4 - Compare the selected Category with the total, excluding the selected category. This allows users to view the performance of a specific category in contrast to the remaining categories ALT Text: A dashboard displaying financial data, including three summary boxes at the top showing total revenue figures of $76,274 for Primary and $187,388 for Revenue Compare. Below, a graph illustrates revenue comparison over time with two lines plotting values for each month. To the left, there is a bar chart comparing revenue by region, highlighting Europe at $130K and USA at $74K. On the right, a table lists employee revenue with columns for employee names, revenue amounts, quantities, and total sales orders. There are filter options on the right side of the image. Best Regards AssafMultiple 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(); })Dashboard Script for Automatic Filter Cascading
Dashboard Script for Automatic Filter Cascading This dashboard script below can be used to synchronize the selections of a set of filters. For example, if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well. The script also replicates the disabled state from the first filter to the other filters. Look below for an example of Elasticube and dashboard. 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. Example Use Case: When dealing with multiple date fields in a fact table, this article provides two options, both of which require duplicating all rows in the fact table as many times as the number of date fields. For example, if you have three date fields that you need to analyze your KPIs by, you'll have to have three copies of the fact table. While these approaches are easy to implement, oftentimes they are not feasible and scalable solutions due to the size of the data. A more scalable alternative is to duplicate the date dimension table, which is substantially smaller than a fact table (e.g. a date dimension table containing 10 years of data only has either 3,652 or 3,653 rows), then connect each date field from the fact to the corresponding date dimension table. See the picture below for an example. The next step is to add each date field as a filter to the dashboard. Keep only the first filter editable and lock the other filters. The subsequent filters will be updated automatically as users set the first filter. Ensure that the filters are correctly toggled on/off in each widget's setting. In the example above, the first widget shows the number of hospital admissions by the admission date. Therefore, only the first (Admission Date) filter should be turned on. The last step is to add the dashboard script that automatically cascades the selection of the first filter to the subsequent filters. In the filterNames variable, specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters. This is the only part of the code that requires your input. /*************************** This script is used to synchronize the selections of a set of filters ***********************/ /** E.g. if the first filter is set to ABC, all subsequent filters listed will automatically be updated to ABC as well ***/ /***************** The script also replicates the disabled state from the first filter to the other filters **************/ //Specify the names of the filters, starting from the first filter, from which the selections will be replicated to other filters //This is the only part of the code that requires your input var filterNames = ['Admission Date', 'Discharge Date', 'Last Visit Date'] //Every time a filter is changed, this code is executed dashboard.on('filterschanged',function(d) { //Find the first filter by name var filter1FilterObject = dashboard.filters.$$items.find((item) => { if (item.jaql && item.jaql.title.indexOf(filterNames[0]) !== -1) { return true; } }); //Get the JAQL filter selection of the first filter var filter1FilterObjectJAQL = filter1FilterObject.jaql; //Get the JAQL filter disabled state of the first filter var filter1FilterObjectDisabled = filter1FilterObject.disabled; //Define an array for the subsequent filters' objects var filterObjects = new Array(); //Find each of the subsequent filters by name for(i=1 ; i<filterNames.length ; i++) { filterObjects[i-1] = dashboard.filters.$$items.find((item) => { if (item.jaql && item.jaql.title.indexOf(filterNames[i]) !== -1) { return true; } }); } //Update the properties of the subsequent filters to match the first filter for(i=0 ; i<filterObjects.length ; i++) { if(typeof filterObjects[i] != 'undefined') { filterObjects[i].jaql.filter = filter1FilterObjectJAQL.filter; filterObjects[i].disabled = filter1FilterObjectDisabled; } if(filter1FilterObjectJAQL.datatype == 'datetime') { filterObjects[i].jaql.level = filter1FilterObjectJAQL.level; } } //Refresh the dashboard dashboard.refresh(); });
