Using Native Javascript Date Calculations To Modify Sisense Date Filters
Using Native Javascript Date Calculations To Modify Sisense Date Filters
Sisense natively supports various types of date filter functionalities. However, there are instances where a dynamically updating date filter is desired based on dynamically changing conditions such as the current date. Such a filter may not precisely align with the filters provided in the Sisense UI. One approach to achieve this custom behavior of a dynamically updating filter is through the use of dashboard or widget scripting.
A particular consideration when dealing with a date filter is that, in most cases, date filter calculations are best done using JavaScript date objects (which use Unix time). Sisense date filters typically utilize dates in standard calendar-based string date formats.
The following script exemplifies this functionality; It generates a date object based on the required custom date behavior, converts it into a string format compatible with Sisense, and then modifies the Sisense date filter with the formatted date string. JavaScript date objects offer numerous useful functions, enabling straightforward mathematical operations such as addition or subtraction to calculate dates based on desired formulas.
This script example specifically identifies dates a set number of days ago (variables set at 60 and 10 days ago from today, respectively) and sets a date filter using these values in the "From" and "To" parameters of a date range filter.
// Programmatically modify Date dashboard filter "From" value to day to a set number of days ago, and the "To" date to different set number of days ago
dashboard.on('initialized', function () {
// Modify to match the relevant date filter title, date filter must already exist and be "From" and "To" date filter
let filterModifiedName = "Date";
// Number of days ago to find date of from "From" date
// Modify to days ago to calculate "From" date
var daysAgo = 60
// Number of days ago to find date of from "To" date
// Modify to days ago to calculate "To" date
var offsetDate = 10
filterModifiedName = filterModifiedName.toLowerCase()
// Variable containing the Dashboard filter modified by script
let modifiedFilter = dashboard.filters.$$items.find(function (filterItem) {
return !filterItem.isCascading && filterItem.jaql && filterItem.jaql.title && filterItem.jaql.title.toLowerCase() === filterModifiedName;
});
// If relevant filter has from and to values selected, date filter must already exist and have from and to values
if (modifiedFilter && modifiedFilter.jaql && modifiedFilter.jaql.filter && modifiedFilter.jaql.filter.from && modifiedFilter.jaql.filter.to &&
daysAgo && daysAgo >= 1) {
// "From" date variable
var fromDate = new Date
// Time in MS of number of daysAgo
var daysAgoInUnixTimeUnits = daysAgo * 24 * 60 * 60 * 1000;
// Subtract daysAgo in MS to find date daysAgo ago
var daysAgoDateUnixTime = fromDate.getTime() - daysAgoInUnixTimeUnits;
// Set date object of date in unix time
fromDate.setTime(daysAgoDateUnixTime)
// Format date for Sisense date format
var formattedFromDate = fromDate.toISOString().slice(0, 10)
// Set date filter "From" value
modifiedFilter.jaql.filter.from = formattedFromDate
// "To" date variable
var toDate = new Date
// Time in MS of number of offsetDate
var daysAgoInUnixTimeUnitsOffset = offsetDate * 24 * 60 * 60 * 1000;
// Subtract offsetDate in MS to find date
var daysAgoDateUnixTimeTo = toDate.getTime() - daysAgoInUnixTimeUnitsOffset;
// Set date object of date in unix time
toDate.setTime(daysAgoDateUnixTimeTo)
// Format date for Sisense date format
var formattedToDate = toDate.toISOString().slice(0, 10)
// Set date filter "To" value
modifiedFilter.jaql.filter.to = formattedToDate
}
});
This script is a slight variation that always sets the "To" date to the current date without offsetting this parameter:
// Programmatically modify Date dashboard filter "From" value to day to a set number of days ago, and the "To" date to current date
dashboard.on('initialized', function () {
// Modify to match the relevant date filter title, date filter must already exist and be "From" and "To" date filter
let filterModifiedName = "Date";
// Number of days ago to find date of
// Modify to days ago to calculate "From" date
var daysAgo = 60
filterModifiedName = filterModifiedName.toLowerCase()
// Variable containing the Dashboard filter modified by script
let modifiedFilter = dashboard.filters.$$items.find(function (filterItem) {
return !filterItem.isCascading && filterItem.jaql && filterItem.jaql.title && filterItem.jaql.title.toLowerCase() === filterModifiedName;
});
// If relevant filter has from and to values selected, date filter must already exist and have from and to values
if (modifiedFilter && modifiedFilter.jaql && modifiedFilter.jaql.filter && modifiedFilter.jaql.filter.from && modifiedFilter.jaql.filter.to &&
daysAgo && daysAgo >= 1) {
// "From" date variable
var fromDate = new Date
// Time in MS of number of daysAgo
var daysAgoInUnixTimeUnits = daysAgo * 24 * 60 * 60 * 1000;
// Subtract daysAgo in MS to find date daysAgo ago
var daysAgoDateUnixTime = fromDate.getTime() - daysAgoInUnixTimeUnits;
// Set date object of date in unix time
fromDate.setTime(daysAgoDateUnixTime)
// Format date for Sisense date format
var formattedFromDate = fromDate.toISOString().slice(0, 10)
// Set date filter "From" value
modifiedFilter.jaql.filter.from = formattedFromDate
// "To" date variable
var todayDate = new Date
// Format date for Sisense date format
var formattedToDate = todayDate.toISOString().slice(0, 10)
// Set date filter "To" value
modifiedFilter.jaql.filter.to = formattedToDate
}
});
Breaking down the script into parts, the initial step in modifying a filter (in this case, a dashboard filter, but applicable to a widget filter) is to find the correct filter object in the dashboard array of filter objects. This is achieved by checking for the filter object whose title matches a given variable in this example. It then checks whether the filter is a calendar range-type date filter.
filterModifiedName = filterModifiedName.toLowerCase()
// Variable containing the Dashboard filter modified by script
let modifiedFilter = dashboard.filters.$$items.find(function (filterItem) {
return !filterItem.isCascading && filterItem.jaql && filterItem.jaql.title && filterItem.jaql.title.toLowerCase() === filterModifiedName;
});
After a conditional check to ensure the date filter was found, the calculation of the date object is accomplished. First, a JS date object is created based on Unix time, defaulting to the current Unix time in UTC. The date is then modified mathematically by, in this case, subtracting from the current Unix time in milliseconds the set number of days to calculate. The days-to-MS calculation is performed using multiplication (hours in a day, minutes in an hour, seconds in a minute, MS in a second). This method can be easily modified to calculate values for years, months, or other time units as needed. Finally, the date object in MS is converted to an ISO format date string (a human-readable calendar string), and the part of the string containing details about units smaller than the day is removed by a straightforward string splice.
// "From" date variable
var fromDate = new Date
// Time in MS of number of daysAgo
var daysAgoInUnixTimeUnits = daysAgo * 24 * 60 * 60 * 1000;
// Subtract daysAgo in MS to find date daysAgo ago
var daysAgoDateUnixTime = fromDate.getTime() - daysAgoInUnixTimeUnits;
// Set date object of date in unix time
fromDate.setTime(daysAgoDateUnixTime)
// Format date for Sisense date format
var formattedFromDate = fromDate.toISOString().slice(0, 10)
// Set date filter "From" value
modifiedFilter.jaql.filter.from = formattedFromDate
// "To" date variable
var todayDate = new Date
// Format date for Sisense date format
var formattedToDate = todayDate.toISOString().slice(0, 10)
// Set date filter "To" value
modifiedFilter.jaql.filter.to = formattedToDate
The filter object was already found in the previous step, so the filter object is modified by adjusting the JAQL filter "To" or "From" parameter of the filter object. No additional code is needed to modify the filter in this case; the filter is modified by directly adjusting the existing parameters of the filter JAQL object.
In this example, the current date is used as the starting point, so the script applied by this filter will change based on the current date.
Below is the result of this filter changing script, using the 60 days to 10 days ago from the current date values set as parameters.
Simpler or more complex calculations or formulas could be applied as required to form the dates required for a particular use case. Any date calculation that is programmatically calculable can then be converted to a calendar date format and used to modify a date filter. Native Sisense allows many different types of data filters, and the JAQL syntax can also be used to create custom filters. However, programmatically setting filters allows for complete flexibility in types of dynamically changing date filters.
The examples above use only the dashboard "initialized" event, and as such, only modify the set filter when the dashboard is first loaded. As a result, it remains possible to modify the filter normally; the script will only modify the filter again on a fresh load of the dashboard, allowing a user to adjust the filter temporarily as required.
While the above example uses the native Sisense filter object, the filter object in Sisense.js is identical to the native Sisense filter object. Therefore, this code can also be used in code determining the date strings to apply with the Sisense.js applyFilter function. Similarly, this type of JS date object calculation can be used to determine the filters to use in a date filter in ComposeSDK, with only the final step of applying the filter differing.
Share your experience in the comments!
