cancel
Showing results for 
Search instead for 
Did you mean: 
JeremyFriedel
Sisense Team Member
Sisense Team Member

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! 

Rate this article:
Comments
Jake_Raz
10 - ETL
10 - ETL

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
Sisense Team Member
Sisense Team Member

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
10 - ETL
10 - ETL

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

JeremyFriedel
Sisense Team Member
Sisense Team Member

No problem Jake, glad to hear!

Jake_Raz
10 - ETL
10 - ETL

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
Sisense Team Member
Sisense Team Member

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
10 - ETL
10 - ETL

Thanks @JeremyFriedel !

SamAlbertCSM
Sisense Team Member
Sisense Team Member

Extremely helpful @JeremyFriedel . 

Helping a customer with a similar request and came across this post. 

Below is a snipped example of the SQL code for one of the connections.

SELECT

*

FROM

Table A

    to_char(A.Timesheetdate, 'MM/DD/YYYY') >= '04/27/2024'

    AND to_char(A.Timesheetdate 'MM/DD/YYYY') <= '05/10/2024'

We need to parameterize the dates (highlighted in bold) so we don’t have to update them manually. The dates need to be changed every week on Friday afternoon. The time period of the date range changes every other week from one week to two weeks depending on the payroll cycle. We do know the rolling dates at the start of the year. Are there any dynamic parameter options available we can leverage? Are there any other solutions approaches?

When we build the data sources, we want to pass the date as parameter values and not hardcode inside SQLs.

 

JeremyFriedel
Sisense Team Member
Sisense Team Member

Hi @SamAlbertCSM ,

 

Glad to hear it is helpful!

 

While there may be more Sisense specific functionality to handle this type of SQL functionality, native non-Sisense specific SQL includes the capability (depending on the exact SQL server in question) to fetch the current date and also to apply calculations to that date (this is pretty specific to the SQL server in question) , such as subtracting a set number of days.

 

 

 

 

Jake_Raz
10 - ETL
10 - ETL

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

    }

});

 

JeremyFriedel
Sisense Team Member
Sisense Team Member

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.

Screen Shot 2024-06-22 at 11.33.08 PM.png

 

Version history
Last update:
‎11-27-2023 10:49 AM
Updated by: