Limiting Date Range Filters in Sisense Dashboards
Use Case Overview
Wide date ranges in Sisense dashboards can lead to performance issues, especially when using live models or querying large datasets. For live data models, large queries increase costs as more data is pulled from the data warehouse. For Elasticubes, this can cause performance bottlenecks. To avoid these issues, it’s essential to limit the date range users can select, ensuring both cost-efficiency and smooth performance.
Solution
To address this, we can use a dashboard-level script that automatically limits the date range. When users apply a date range filter, the script checks if the range exceeds a defined maximum (e.g., 30 days). If it does, the script adjusts the FROM date to be within the limit while keeping the TO date as the user selected.
Key Insight:
The “filterschanged” event is triggered before the query is sent to the backend. This means the query is only sent once, with the modified date range, avoiding redundant queries that could increase load or costs.
Implementation
Here’s the concise script for implementing this logic:
dashboard.on('filterschanged', function(el,args){
//console.log(args);
//**************** User Input ****************
var datefilterTable = "dim_date";
var datefilterColumn = "date";
var allowedDateRangeInDays = 30;
var warningMessage = `Your From Date is modified to accommodate for the allowed date range of ${allowedDateRangeInDays} days`;
var displayWarningMessage = true; // Set to false to turn off alert
//********************************************
if(args.items.$$items){
if((args.items.$$items).length > 0){
(args.items.$$items).forEach( (item) => {
if(item.jaql.table == datefilterTable && item.jaql.column == datefilterColumn ){
var dateFilter = item;
var fromDateStr = item.jaql.filter.from;
var toDateStr = item.jaql.filter.to;
const fromDateObj = new Date(fromDateStr);
const toDateObj = new Date(toDateStr);
// Calculate the difference in days
const currentDateRangeInTime = toDateObj - fromDateObj;
const currentDateRangeInDays = currentDateRangeInTime / (1000 * 3600 * 24);
// Checking if the Current Date Range is more than the allowed range
if(currentDateRangeInDays > (allowedDateRangeInDays+1)){
const newFromDateObj = new Date(toDateObj);
newFromDateObj.setDate(toDateObj.getDate() - (allowedDateRangeInDays));
// Format the date back to "YYYY-MM-DD"
const year = newFromDateObj.getFullYear();
const month = String(newFromDateObj.getMonth() + 1).padStart(2, '0');
const day = String(newFromDateObj.getDate()).padStart(2, '0');
const newFromDateString = `${year}-${month}-${day}`;
dateFilter.jaql.filter.from = newFromDateString;
// Display warning message and update filter
if(item.jaql.filter.to != newFromDateString){
console.log(warningMessage);
if(displayWarningMessage){window.alert(warningMessage)};
args.dashboard.filters.update(dateFilter, {refresh: true, save: true});
}
}
}
})
}
}
});
Key User Parameters:
- datefilterTable: The table containing the date filter.
- datefilterColumn: The specific date column to watch.
- allowedDateRangeInDays: Maximum allowed date range (e.g., 30 for 30 days).
- warningMessage: The message displayed if the date range is modified.
- displayWarningMessage: Controls whether a pop-up alert is shown.
Conclusion
This simple script ensures that user-selected date ranges stay within defined limits, improving performance and reducing costs. It adjusts the FROM date automatically, while the TO date remains as chosen, and ensures that only one query is sent to the backend—saving resources and improving efficiency.
Additional Resources:
https://academy.sisense.com/master-class-advanced-dashboards-with-plug-ins-and-scripts
https://docs.sisense.com/main/SisenseLinux/customizing-sisense-using-code.htm
