Expanding on Using Native Javascript Date Calculations To Modify Sisense Date Filters

[JeremyFriedel]

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!