Sisense widget script: Pass only "to date" from dashboard Calendar filter to the widget

In this tutorial, we'll see how we can pass only the end date from a dashboard calendar filter to a Sisense widget using script. This script enables your widget to dynamically respond to the selected end date from the calendar filter. Follow the step-by-step instructions below to implement this feature.

In Sisense, when we apply the Calendar filter, both the "from" and "to" date is passed to the widget i.e. a date range is passed.

e.g. From date: 1st January 2023 To date: 31st January 2023

Measure: Count of Items Here, we'll get the Count of Items between 1st January 2023 and 31st January 2023.

However, in some cases we want to know the value as of a certain end date. Suppose I want to know the Number of Covid Cases as of 31st January 2023. In this case, passing only the "to date" is required.

Let's see how we can achieve this.

Step 1: Adding the widget script

Begin by opening your Sisense dashboard in the Edit mode. Locate the indicator widget for which you want to implement this. Add the following code into the widget's script editor.

widget.on("beforequery", function(se, ev) {
	
    // Get the metadata items from the query
    let metadataItems = ev.query.metadata;

    // Define the filter title to target
    // Instead of Date, add the name of your filter
    let filter = "Date";

    // Get the current date
    const date = new Date();
    let day = ("0" + date.getDate()).slice(-2);
    let month = ("0" + (date.getMonth() + 1)).slice(-2);
    let year = date.getFullYear();
    let currentDate = `${year}-${month}-${day}`;

    // Iterate through the metadata items
    metadataItems.forEach(function(item) {
		
        // Check if the item belongs to the "scope" panel,
        // has the specified filter title, and is of datetime datatype
        if (
      		item.panel === "scope" &&
            item.jaql.title === filter &&
            item.jaql.datatype === "datetime" &&
            item.jaql.filter &&
            item.jaql.filter.to
        	) 
		{
            // Check if end date > current date, assign current date
            if (item.jaql.filter.to > currentDate) 
			{
                item.jaql.filter = 
				{
                    members: [currentDate]
                }
            } 
			// Otherwise, use the selected end date
			else 
			{
                item.jaql.filter = 
				{
                    members: [item.jaql.filter.to]
                }
            }
        }
    })
});

Step 2: Understanding the Code Logic

The widget.on("beforequery", function(se, ev) {...}) event handler triggers before the widget query is executed.

Then we retrieve the metadata items from the query and get the filter's title.

We find the calendar date filter by specifying various conditions like title, datatype, determining whether it has "to date" item i.e. item.jaql.filter.to, etc.

Get the current date from JavaScript Date object and format it for comparison with the "to date".

Then we access whether the selected "to date" is greater than the current date. If it is then, current date is passed to the widget; otherwise, the selected "to date" is passed.

Step 3: Applying the Changes

After adding the script, click "Save" to apply the changes and refresh the widget. Now, the selected "to date" will be passed to the widget every time to calculate the value.

Before:

After:

GitHub link : https://github.com/lekhamirjankar/Sisense-scripts