How to have dashboard filters affect a formula filer?
Hi DRay , I have this formula in Sisense Bar Chart: (((([Total CLAIM_CNT]) )/([Total CLAIM_CNT],Prev([@Months in DATE], 12),([@Network Name])))-1) Where I have the Category being Network Name, I also have the Network Name in the Dashboard filter but when I select a Network Name in the Dashboard filter the widget does not filter for my selected Network Name. The Network Name being in the Denominator is to get the Total Claim Count. I also know that the hierarchy for filtering in Sisense is Formula Filter > Widget fitler > then Dashboard filter. Is there anyway to override this behavior? I still need to the overall claim counts to get the percentage by individual network names but still want to filter for the specific network name.
Advanced Dynamic Filtering
Get quick, comparative insights from large datasets by creating flexible pivot tables or widgets. Whether comparing time periods, countries, or sales figures, you can leverage a single filter to handle multiple comparisons without clutter. This method uses custom fields, measured values, ranking filters, and the smart label plugin to streamline comparisons across various dimensions for clean, dynamic visualizations.Filter to only show Relevant Dimension Values
Morning Sisense community! I have my Fact Table and a related Dimension Table. My dimension table contains more unique values than are actually present in the fact table. When I use a field from the Dimension Table as a filter, the filter displays all values from the dimension, even those that have no corresponding fact records. I only want the filter to show dimension values that are relevant to the data in the fact table. Example Tables: fact_table dimension_table id dim_id dim_id dim_name 1 1 1 one 2 2 2 two 3 1 3 three 4 2 4 four fact_table and dimension_table have a relationship in the EC based on dim_id Desired Outcome: In the filter based on dim_name, I only want to see "one" and "two", but currently, I see all four: "one," "two," "three," and "four." How do I achieve my desired outcome?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.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.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.
