Using Native Javascript Date Calculations To Modify Sisense Date Filters
Published 11-27-2023
Hello, me again! How would I modify the script so that the "From" date is always the first of the current year and the "To" date is always the last day of the previous month? So, for example, if today is 6/19/24 the filter will be set to 1/1/24 - 5/31/2024, or if today is 7/19/24 then the filter will be set to 1/1/24 - 6/30/24.
I tried using the below script but it will not work, the filter will not automatically update 😞 (it's for the first day of the prior month, not the last, but it still won't work anyway, I'm not sure what I did wrong)
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 = "GV File Date";
// Number of days ago to find date of (used for rolling date calculations)
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, always first day of current year
var fromDate = new Date(new Date().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(new Date().getFullYear(), getMonth()-1, 1)
// Format date for Sisense date format
var formattedToDate = toDate.toISOString().slice(0, 10)
// Set date filter "To" value
modifiedFilter.jaql.filter.to = formattedToDate
}
});