Using Native Javascript Date Calculations To Modify Sisense Date Filters

Hi Jake,

Yes, this is possible, but the code for selecting the dates will need to be hosted in a script, as native Sisense filters do not include day-of-the-week functionality.

Sisense allows any list of dates to be set as filter members, so custom code can be used to generate a list of dates within a date range that fall on specified days of the week, formatted as Sisense expects.

Here's an example of how you can achieve this:

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

    // Map weekdays to their corresponding numerical representation
    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'); // months are zero-indexed
            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);

Alternatively, it is possible to create a custom column as a separate Sisense dimension to store the day of the week for each date. This would allow you to filter by day of the week using the native UI.

The parameter path for modifying an existing dashboard filter date member list (list of valid dates) is:

prism.activeDashboard.filters.$$items[0].jaql.filter.members