Sisense dashboard script: Custom "to date"/"from date" filter

In this tutorial, we'll learn how to enhance your Sisense dashboards by implementing a custom date range ("to date" and "from date") feature using dashboard script. This feature ensures that the date filter always starts from and ends with a specific date; and sets this as the default date.

Step 1: Adding the dashboard script

Begin by opening your Sisense dashboard in the Edit mode. Add the following code into the dashboard's script editor.

// Event handler executed when the dashboard is initialized
dashboard.on('initialized', function(s, e) {

	// Retrieve the specific date filter to be customized
	// Instead of Commerce.Date add your Table.Column name
	let newFilter = e.dashboard.filters.item('[Commerce.Date (Calendar)]', 'days');

	// Get the current date and format it
	let toDate = new Date();
	let toDateDay = ("0" + toDate.getDate()).slice(-2);
	let toDateMonth = ("0" + (toDate.getMonth() + 1)).slice(-2);
	let toDateYear = toDate.getFullYear();
	let toDateParsed = `${toDateYear}-${toDateMonth}-${toDateDay}`;

	// Determine the start date based on a condition
	if (toDateMonth >= 2) {
		var fromdate = new Date(toDateYear, 2, 1); // Start from March
	} else {
		var fromdate = new Date(toDateYear - 1, 2, 1); // Start from March of the previous year
	}

	// Format the start date
	let fromDateDay = ("0" + fromdate.getDate()).slice(-2);
	let fromDateMonth = ("0" + (fromdate.getMonth() + 1)).slice(-2);
	let fromDateYear = fromdate.getFullYear();
	let fromDateParsed = `${fromDateYear}-${fromDateMonth}-${fromDateDay}`;

	// Update the filter with custom date range
	newFilter.jaql.filter.from = fromDateParsed;
	newFilter.jaql.filter.to = toDateParsed;

	// Apply the changes to the filter and default filters
	e.dashboard.filters.update(newFilter, { refresh: true, save: true });
	e.dashboard.defaultFilters.find(f => f.jaql.dim === '[Commerce.Date (Calendar)]').jaql.filter.from = fromDateParsed;
	e.dashboard.defaultFilters.find(f => f.jaql.dim === '[Commerce.Date (Calendar)]').jaql.filter.to = toDateParsed;
});

Step 2: Understanding the Code Logic

The dashboard.on('initialized', function(s, e) {...}) event handler is triggered when the dashboard is initialized.

Find the required date filter [Commerce.Date (Calendar)] and level 'days' (need to specify level in case there is another dashboard filter coming from same column with level 'months', 'years', etc.).

Then we get the current date from JavaScript date object, format it and also use it to determine the start date based on a condition. Here, we are trying to set the date range to depict Fiscal start to date scenario.

In this case, the fiscal starts from March. If the current month is greater than or equal to March, the start date is set to March of current year; otherwise, it's set to March of the previous year.

You can use any condition in the above step that suits your requirement or even hard code the "to date" and "from date".

The start and end dates are formatted and updated in the filter.

Changes are applied to both the filter and the default filters to ensure consistent behavior.

Step 3: Applying the Changes

After adding the script, click "Save" to apply the changes and refresh the dashboard. Now, the dashboard date filter will reflect the customized selections.

This enhancement ensures a more tailored user experience and helps users begin their analysis with relevant date ranges.

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