Setting Date Filter Members to Specific Days of the Week in Sisense Programmatically

[JeremyFriedel]

Setting Date Filter Members to Specific Days of the Week in Sisense Programmatically

Sisense natively supports a wide range of date filter functionalities and formats. However, certain scenarios may call for unusual custom programmatic forms of date filtering, such as filtering to specific days of the week within a defined date range. For example, focusing a dashboard view on only Wednesdays in a given period, or on weekend dates, can yield valuable insights in time-sensitive analyses. This cannot be directly configured with standard Sisense UI filters when only one date dimension is present in the data source.

In the previous article, the concept of date calculations was demonstrated by leveraging JavaScript date objects, modifying filter parameters programmatically via dashboard or widget scripting, and converting those date objects into formats Sisense expects. (For reference, see: Using Native Javascript Date Calculations To Modify Sisense Date Filters) The same principle can be applied to "Day of the Week" based filtering. Sisense allows defining a custom set of date members as a filter. By dynamically constructing a list of dates that fall on certain days of the week within a chosen date range, it is straightforward to pass these custom members into Sisense filters, setting them as members of a date member filter and creating a selective date filter.

In the snippet below, JavaScript generates a list of dates falling on specified weekdays—such as Saturdays and Sundays—within a given start and end date range. It then formats them into the string format Sisense expects for filter members. These can be applied within the filter object just as demonstrated in the previously linked article on programmatic date filter modification.

function getDatesOnWeekdays(weekdays, startDate, endDate) {
    const dayNameToNumber = {
        "Sunday": 0,
        "Monday": 1,
        "Tuesday": 2,
        "Wednesday": 3,
        "Thursday": 4,
        "Friday": 5,
        "Saturday": 6
    };

    // Convert weekday names to their numeric values
    const weekdaysNumbers = weekdays.map(day => dayNameToNumber[day]);

    // Convert startDate and endDate to Date objects
    let start = new Date(startDate);
    let end = new Date(endDate);

    // Initialize result array
    const result = [];

    // Iterate from startDate to endDate
    for (let date = new Date(start); date <= end; date.setDate(date.getDate() + 1)) {
        // Check if the day of the week matches the specified weekdays
        if (weekdaysNumbers.includes(date.getDay())) {
            // Format date as "YYYY-MM-DDT00:00:00"
            const year = date.getFullYear();
            const month = String(date.getMonth() + 1).padStart(2, '0');
            const day = String(date.getDate()).padStart(2, '0');
            const formattedDate = `${year}-${month}-${day}T00:00:00`;
            result.push(formattedDate);
        }
    }
    return result;
}

// Example usage:
const weekdays = ["Saturday", "Sunday"];
const startDate = "2024-11-01";
const endDate = "2024-11-30";
const dates = getDatesOnWeekdays(weekdays, startDate, endDate);
console.log(dates); 
​

This example produces a list of dates that occur only on Saturdays and Sundays within the specified date range, formatted as YYYY-MM-DDT00:00:00, which is the format Sisense used for date filters. For example: ["2024-11-02T00:00:00", "2024-11-03T00:00:00", "2024-11-09T00:00:00", "2024-11-10T00:00:00"].

Once this list is generated, set the members property of the filter’s JAQL object to these values.

// Assuming modifiedFilter is a date filter object already obtained from the dashboard filters
modifiedFilter.jaql.filter.members = dates;

The dashboard filter now includes exclusively the targeted weekdays.

This approach depends on programmatic customization. Native Sisense filters do not include a direct "Day of the Week" capability, but a script can calculate the exact set of valid dates meeting any specific criteria, which can then be used with Sisense filter objects to modify the filter programmatically. Since the code directly modifies the filter’s member list, it can be combined with previously illustrated techniques for dynamically setting “From” and “To” dates, offsetting filters based on current or relative dates, and applying other conditional logic. For example, more complex logic could be implemented to filter only the first Tuesday of each month or other patterns.

For even more flexibility and to avoid custom code, consider creating a custom field in the underlying data model that stores the day of the week for each date, enabling direct filtering by that custom dimension in the standard Sisense UI. This approach trades off some versatility for simpler maintenance and configuration.

As shown, the same principles that enable filters to be dynamically set to certain date ranges can also be applied to filter on "Day of the Week" criteria. This builds on the earlier article’s demonstration of using JavaScript date calculations to craft custom filter conditions, offering an even wider range of data filtering possibilities.

Related Content:

Docs: https://docs.sisense.com/main/SisenseLinux/customizing-sisense-using-code.htm

Dev: https://sisense.dev/guides/customJs/extensions/

Sisense Academy: https://academy.sisense.com/master-class-advanced-dashboards-with-plug-ins-and-scripts