Change Continuous Timeline on Charts to Show Date every 7 days instead of every day

[jmn3]

Problem:

One of our teams uses Sunday as the calendar week start day, which is different than our other teams that use Monday as the week start day. I have created a special column in a date dimensional table where the date reflects Sunday and am using this field to aggregate my data weekly. I left the date dimension on this new value as "day" since changing it to "weekly" would cause the Monday date to display and I need the date for Sunday.

My problem is that now when I use the Continuous Timeline feature to fill in any missing dates, the chart will populate all the days instead of the missing week. So if I have data for 2022-05-15 and 2022-05-29 but not for the middle week, 2022-05-22, and I select Continuous Timeline, I get the following:

I was wondering if anyone knew of a way that I can change the logic using a script so that the Continuous Timeline will show missing dates in 7 day intervals instead of 1 day intervals or maybe had another workaround? I tried modeling I the elasticube, but the dashboard has many filters and the data needs to be dynamic.

I have a chart that I want to display missing date values on the x-axis but I only want to fill missing values for every 7 days. I

Example: I have data for 2022-05-15 and 2022-05-29.

[harikm007 · Accepted solution]

Hi @jmn3 ,

Try below script. Don't use Continuous Timeline feature in Sisense

(I have done some basic testing with this script)

widget.on("processresult", function (se, ev) {

	max_date = new Date(ev.result.series[0].data[ev.result.series[0].data.length - 1].selectionData[0])
	min_date = new Date(ev.result.series[0].data[0].selectionData[0])
	
	day_interval = 7 //Update the interval here
	
	var new_date_list = [];
	var new_date_list_string = []
	
	
	temp_date = min_date
	while (temp_date <= max_date) {
        new_date_list.push(new Date(temp_date));
		new_date_list_string.push(moment(temp_date).format('ll'));
        temp_date.setDate(temp_date.getDate() + day_interval);
    }
	
	ev.result.series.forEach( function(bb) {
		newData= new Array(new_date_list.length);
		var blankcat;
		bb.data.forEach( function(cat) {
			if (cat.selectionData){
				newData[new_date_list.map(Number).indexOf(+cat.selectionData[0])]=cat;
			}
		})
		for (i=0; i<newData.length; i++) {
			
			blankcat = {
								"y": null,
								"color": null,
								"selected": false,
								"marker": {
									"fillColor": "white",
									"lineColor": null
								}
							}
			
			if (typeof newData[i] === 'undefined') {
				newData[i]=blankcat;	
			}
		}
		bb.data=newData;
	});

	ev.result.xAxis.categories = new_date_list_string
})

-Hari

[jmn3]

This works great!  Do you know how I should format the new date so that it will display the calendar year instead of the fiscal year by chance? Right now, it shows the year is 2023 but the calendar year is 2022. 

Thank you for your help on this!