BloX - Date and Timestamp Range Filter

Sisense doesn't currently support filtering on timestamps natively. This article shows how we can leverage BloX to work around this limitation and allow users to select a date and timestamp range to filter their dashboards. (This is an updated version of this old community article, shout out to Kaitleen Crowe!)

1. Create a numeric date-time column in the data model 

The goal here is to convert the date-time field you wish to filter by to a BIGINT in the format of YYYYDDMMHHMMSS. Let's break that down. 

• Choose your date-time field to convert.
• Create a new custom column in the same table.
• Convert the date-time field to a BigInt with the format YYYYDDMMHHMMSS.
• Your values should not have '/' or '-'. If you have these characters, they will need to be parsed out.

Example:

In this example we will be converting the [Admission_Time] field into a custom column [Admission_Time_Int] as a BIGINT. **Note: Your date formats can vary so the method to convert will vary as well. 

-- Changing datatype to  bigint

toBigInt(

--formatting date

replaceAll(StrParts(toString([Admission_Time]), ' ', 1), '-', '')  +

-- formatting time             

replaceAll(left(SubString(toString([Admission_Time]), 12), 8), ':', ''))

2. Add BloX widget to the dashboard

• Add a new BloX widget to your dashboard.
• Upload the BloX template attached in this article (file DateTimeFilter-2022-11-08.json) to your instance like shown in the screenshot below.

• Once the template is loaded in your Editor tab, scroll to the ActionSet and under actions, change the NumDateTimeColumn value to the name of the table and column of the numeric date-time that you created in the first step. The format is tableName.columnName.

3. Create a new custom action

• Create a new BloX action like shown below.

• Copy and paste the Javascript code below into the custom action editor:

var datetimeCol = payload.data.NumDateTimeColumn;
var dash = payload.widget.dashboard;

//grab the start date and start time and parsing values
var dt1 = payload.data.datevalstart;
dt1 = dt1.substring(0, 4) + dt1.substring(5, 7) + dt1.substring(8);
var t1 = payload.data.timevalstart;

if (t1 == '') {
    t1 = '00:00';
}

t1 = t1.substring(0, 2) + t1.substring(3);
var dtstart = parseInt((dt1 + t1 + '00'), 10);

//grab the end date and end time and parsing values
var dt2 = payload.data.datevalend;
dt2 = dt2.substring(0, 4) + dt2.substring(5, 7) + dt2.substring(8);
var t2 = payload.data.timevalend;

if (t2 == '') {
    t2 = '23:59';
}

t2 = t2.substring(0, 2) + t2.substring(3);
var dtend = parseInt((dt2 + t2 + '00'), 10);

//create JAQL filter definition
let newFilter = {};

newFilter = {
    jaql: {
        dim: "",
        filter: {
            from: dtstart,
            to: dtend
        }
    }
}

//apply the filter
datetimeCol.forEach(function (dim) {
    newFilter.jaql.dim = dim;
    dash.filters.update(newFilter, { refresh: true, save: true })
})

• Name the action DateTimeSelector (or any other name you'd like), press Next then Create to save the new action. If you choose to give the action a different name, do the following additional steps:
  • Copy the name of the new action.
  • Click the Editor tab.
  • Scroll to the ActionSet and under actions, change the type to the name of the action you created.

4. Add the numeric Date-Time column to the dashboard filter panel.

• Add the numeric date-time column you created in the first step to the dashboard filter panel.
• When adding this filter, go to the Values tab and select the 'Between' option 
• Press OK.

You now have a selector for date and time. Select a date and time in the picker input fields and press the Filter button.

5. Improve User Experience by making the input fields remember previous selection

You will notice that after you hit the Filter button, even though your selection has been applied to the dashboard filter, the BloX widget will refresh and your previous selection will be wiped from the date and time selectors. To prevent this, you can turn off dashboard filters in the BloX widget setting. Open the BloX widget, go the Filters tab on the right panel, then turn off the Dashboard Filters option. Once this option is turned off, the BloX widget will not refresh when you hit the Filter button.

Note that after a page/dashboard reload, the BloX widget will still refresh and wipe the previous selection from the date and time input fields.

If you'd like to further improve the User Experience by having the date and time input fields to remember the previous selection even after a page refresh, follow these steps:

• Open the BloX widget.
• In the Filters tab on the right panel, turn the Dashboard Filters option back on. Turn on only the numeric date-time filter and leave every other dashboard filter turned off.

• Click Apply and then open the BloX widget again.
• Open the widget script editor by click the three-dot menu of the widget, then click Edit Script.

• Copy and paste the Javascript code below into the widget script editor.

//***** Populate input boxes with current filter values *****/
widget.on('ready', function() {
	
	//replace with filter name
	var filterName = "Admission Time";

	// ----------Find date filter----------
    let dateFilter = dashboard.filters.$$items.find((item) => {
        if (item.jaql && item.jaql.title.indexOf(filterName) !== -1) {
            return true
        }
    })
		
	//get the current 'from' filter value and parse it into date / time format
	var fromDateTimeString = String(dateFilter.jaql.filter.from);
	var fromDate = fromDateTimeString.slice(0,4) + '-' + fromDateTimeString.slice(4,6) + '-' + fromDateTimeString.slice(6,8);
	var fromTime = fromDateTimeString.slice(8,10) + ':' + fromDateTimeString.slice(10,12);// + ' ' + fromAMPM;
	
	//check if current 'from' filter value is a valid date / time
	var checkInvalid_from = typeof dateFilter.jaql.filter.from == 'undefined' || dateFilter.disabled == true;
	var filterDateValue_from = (checkInvalid_from) ? 'someInvalidDate' : fromDate;
	var filterTimeValue_from = (checkInvalid_from) ? 'someInvalidTime' : fromTime;
		
	//set the current 'from' filter value as placeholder and default value for input field
	$('#datevalstart', element).attr('value', filterDateValue_from);
	$('#timevalstart', element).attr('value', filterTimeValue_from);
	
	//get the current 'to' filter value and parse it into date / time format
	var toDateTimeString = String(dateFilter.jaql.filter.to);
	var toDate = toDateTimeString.slice(0,4) + '-' + toDateTimeString.slice(4,6) + '-' + toDateTimeString.slice(6,8);
	var toTime = toDateTimeString.slice(8,10) + ':' + toDateTimeString.slice(10,12);// + ' ' + fromAMPM;	
	
	//check if current 'to' filter value is a valid date / time
	var checkInvalid_to = typeof dateFilter.jaql.filter.to == 'undefined' || dateFilter.disabled == true;
	var filterDateValue_to = (checkInvalid_to) ? 'someInvalidDate' : toDate;
	var filterTimeValue_to = (checkInvalid_to) ? 'someInvalidTime' : toTime;
		
	//set the current 'to' filter value as placeholder and default value for input field
	$('#datevalend', element).attr('value', filterDateValue_to);
	$('#timevalend', element).attr('value', filterTimeValue_to);
	
});

• Replace the var filterName value in the script (line 5) with the name of your date-time dashboard filter.

• Click Save. Go back to your dashboard and refresh the page.
• Your BloX date and time selectors should now be populated with the current filter values, i.e. the values you previously selected.