Using Native Javascript Date Calculations To Modify Sisense Date Filters

JeremyFriedel · ‎11-28-2023

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.

Screen Shot 2023-11-21 at 7.30.17 PM.png

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!

Jake_Raz · ‎01-30-2024

Thanks! Having a script to set the "to" date to the current date is really helpful for my purposes. However, I need help with the "from" date. Your examples seem to only work for a rolling "from" date (e.g. always X days prior to the current date), but I was hoping for a way to set an anchor for a specific time period. It would still be dynamic, in the sense that it'll change whenever the time period shifts, but within that time period it would be static. Know what I mean? For instance, I'd like to set the "from" date to always be the beginning of the current year (i.e. Jan 1st), or other dates like the start of the current quarter/month/etc? I figured out how to specify a purely static date like "1/1/24" but obviously that would need to be updated manually, which isn't ideal.

JeremyFriedel · ‎01-30-2024

Hi Jake,

Glad this article and code was helpful!

A similar approach can be used for any date format whether for the "from" or "to" parameter , where the date is defined in Javascript as a date object and converted to a string for either the "from" or "to" parameter".

Any valid date object can be used, including date objects dynamically defined to the beginning of the year or quarter, and which will automatically update.

Here is code to automatically set the "from" parameter to the first date of the current year, and which will dynamically update:

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, 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
        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

    }

});

Similarly, this is the code to set the "from" parameter to always use the first day of the current quarter:

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) {

		const today = new Date();
		// Current quarter of year
		const quarter = Math.floor((today.getMonth() / 3));
		
		// First day of the current quarter
		var fromDate = new Date(today.getFullYear(), quarter * 3, 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

    }

});

A similar format can be used for any date that can be defined programmatically.

If either the "from" or "to" parameter does not need to be defined programmatically the code modifying this parameter can be removed, leaving only the code modifying the other parameter.

Jake_Raz · ‎01-31-2024

@JeremyFriedel Thanks, those revised scripts worked perfectly. I really appreciate it!

JeremyFriedel · ‎01-31-2024

No problem Jake, glad to hear!

Jake_Raz · ‎01-31-2024

Sorry, one last question: how would I use this to set multiple date filters? I was originally using it to automatically set the filter for an "Open Date", but I just realized I need to do the same for a "Closed Date" filter as well (they apply to different widgets, and I'm turning on/off the appropriate dashboard-level filter from within each widget as necessary).

Is it okay to just copy/paste the entire script again, with the filter name variable modified for the second one, so there's two sets of the script running one after the other? Like so:

dashboard.on('initialized', function () {
    let filterModifiedName = "Opened Date";
   //...rest of the code for setting "Opened Date"...
});

dashboard.on('initialized', function () {
    let filterModifiedName = "Closed Date";
   //...rest of the code for setting "Closed Date"...
});

Would this cause issues? Or, perhaps, is there a better way to implement this? I assume it'll be different if you're setting all the filters to the same values (e.g. both are set to 1st of the year through today) vs setting each to something different (e.g. Opened is 1st of the year through today but Closed is 1st of the quarter through today).

JeremyFriedel · ‎01-31-2024

Yes, that is a possible way of implementing this functionality, this avoids having to redeclare or redefine variables.

It is also possible to wrap the code in a function and pass in variables as needed, such as the name of the filter or other parameters that might be needed. A function can be called multiple times, in this case with different filter names. In this example the "dateFilter" function can be called as many times as needed.

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) {


            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");

});

Jake_Raz · ‎02-05-2024

Thanks @JeremyFriedel !

Sisense Community

Using Native Javascript Date Calculations To Modify Sisense Date Filters

Using Native Javascript Date Calculations To Modify Sisense Date Filters

Share your experience in the comments!