Expanding on Using Native Javascript Date Calculations To Modify Sisense Date Filters
Expanding on Using Native Javascript Date Calculations To Modify Sisense Date Filters
Sisense offers native support for various types of date filter functionalities and also allows users to create and modify dynamic filters entirely using dashboard or widget scripting. This article expands upon the code samples discussed in the previous article on this subject, showcasing additional examples. These examples further demonstrate how to leverage JavaScript date objects (which use Unix time) for advanced date filtering in Sisense. Sisense date filters typically utilize dates in standard calendar-based string date formats.
In these examples, the same format described in the preceding article on this topic is used. A JavaScript date object is created, based on the required custom date behavior and the automatically updated current date. This object is then converted into a string format compatible with Sisense, allowing it to be used directly in the filter "From" and "To" parameters. JavaScript date objects, which natively include many useful date-related functions and capabilities, enable straightforward mathematical operations to dates such as addition or subtraction. These functions allow the programmatic creation of data objects based on any desired formulas or programmatic logic, allowing for greater flexibility in creating dynamically updated filters.
Filtering from the First Day of the Current Year to Today's Date (Year to Date - YTD)
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";
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) {
// "From" date variable, January 1st of Current Year, "YTD"
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
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
}
});
Filtering from the First Day of the Current Month and Year to Today's Date (Month to Date - MTD
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";
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) {
var date = new Date();
var currentYear = date.getFullYear();
var currentMonth = date.getMonth();
// First Day of the current month
var fromDate = new Date(currentYear, currentMonth, 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
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
}
});
Filtering from the First Day of the Current Week to Today's Date (Week to Date - WTD)
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";
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) {
var today = new Date();
var fromDate = new Date();
var previousSunday = new Date();
// First day of current week
previousSunday.setDate(today.getDate() - (today.getDay()) % 7);
fromDate.setDate(previousSunday.getDate());
// 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
}
});
Any other date object can be used to modify the "From" and "To" parameters, defined using the needed logic. If either the "From" or "To" filter parameter does not need to be defined programmatically, the corresponding code modification can be removed, leaving only the code for the other parameter.
For scenarios requiring multiple date filters to be modified, the relevant code can be defined as a function and called multiple times with different parameters. An example implementation is provided below:
dashboard.on('initialized', function () {
function dateFilter(filterName) {
// Modify to match the relevant date filter title, date filter must already exist and be "From" and "To" date filter
let filterModifiedName = filterName;
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) {
// Replace date logic as needed, including any function parameters or conditional statements to define the date based on which filter is being set
var date = new Date();
var currentYear = date.getFullYear();
var currentMonth = date.getMonth();
// First Day of the current month
var fromDate = new Date(currentYear, currentMonth, 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
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
}
}
dateFilter("Date");
});
Share your experience in the comments!
