// 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!
Hi @Jake_Raz!
No problem, the following code has that requested functionality:
dashboard.on('initialized', function () {
// Modify to match the relevant date filter title, date filter must already exist and be a "Calendar" style filter (with specific "From" and "To" dates)
let filterModifiedName = "DateFilterName";
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) {
// Today's date
var currentDate = new Date();
// "From" date variable, always first day of current year
var fromDate = new Date(currentDate.getFullYear(), 0, 1);
// 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, always first day of previous month
var toDate = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0)
// Format date for Sisense date format
var formattedToDate = toDate.toISOString().slice(0, 10)
// Set date filter "To" value
modifiedFilter.jaql.filter.to = formattedToDate
}
});
The issue that was stopping the code shared in the previous comment was that date functions such as getMonth() are only present on a date object and can not be called independently of a date object. This can be done as simply as the following code:
new Date().getFullYear()
new Date().getMonth()It can also be called from an existing date object and not necessarily a new date object on each use. New date objects when created unless provided a different date are always the current date.
The best way to set the last day of the month as the filter value is to use the month in the future (currentDate.getMonth() + 1) but set the day of the month value to zero, this is equivalent to the last day of the current date, this avoids having to include custom code to determine the last day of the current month, which varies from month to month. This can be a bit unintuitive at first, but is a known Javascript workaround for this kind of date functionality.
Hi Jake,
Yes, this is possible, but the code for selecting the dates will need to be hosted in a script, as native Sisense filters do not include day-of-the-week functionality.
Sisense allows any list of dates to be set as filter members, so custom code can be used to generate a list of dates within a date range that fall on specified days of the week, formatted as Sisense expects.
Here's an example of how you can achieve this:
function getDatesOnWeekdays(weekdays, startDate, endDate) {
const dayNameToNumber = {
"Sunday": 0,
"Monday": 1,
"Tuesday": 2,
"Wednesday":3,
"Thursday": 4,
"Friday": 5,
"Saturday":6
};
// Map weekdays to their corresponding numerical representation
const weekdaysNumbers = weekdays.map(day => dayNameToNumber[day]);
// Convert startDate and endDate to Date objects
let start = new Date(startDate);
let end = new Date(endDate);
// Initialize result array
const result = [];
// Iterate from startDate to endDate
for(let date = new Date(start); date <= end; date.setDate(date.getDate() + 1)) {
// Check if the day of the week matches the specified weekdays
if (weekdaysNumbers.includes(date.getDay())) {
// Format date as "YYYY-MM-DDT00:00:00"
const year = date.getFullYear();
const month = String(date.getMonth() + 1).padStart(2, '0'); // months are zero-indexed
const day = String(date.getDate()).padStart(2, '0');
const formattedDate = `${year}-${month}-${day}T00:00:00`;
result.push(formattedDate);
}
}
return result;
}
// Example usage:
const weekdays = ["Saturday", "Sunday"];
const startDate = "2024-11-01";
const endDate = "2024-11-30";
const dates = getDatesOnWeekdays(weekdays, startDate, endDate);
console.log(dates);
Alternatively, it is possible to create a custom column as a separate Sisense dimension to store the day of the week for each date. This would allow you to filter by day of the week using the native UI.
The parameter path for modifying an existing dashboard filter date member list (list of valid dates) is:
prism.activeDashboard.filters.$$items[0].jaql.filter.members
