Display values continuously over time with existing gaps in FACT data table

Question

Hi All,I have the following use case:I want to visualize the overall stock level of 2 different products over time. The source for the stock levels is a FACT table containing the log data of the stock movements. The FACT table is connected to a date dimension table containing the date of every day (see screenshot attached). However, if there was not a movement for a specific product, there is no entry in the FACT table.Example:01.01.22: stock level = 502.01.22: stock level = 406.01.22: stock level = 10When displaying those stock levels on a line/column chart, I only display the 3 days by default. There exists a "time gap" in the data and I am looking for a way to display the stock level for every day as a "continuous" rate over time. As there are no values in the FACT table for 03/04/05th January, I would like to display the latest value available (in this case the value stock level = 4).Are there any ways to solve this on the dashboard side (maybe with function/script)?I have already enabled the widget option "Display missing values as 0" but instead of displaying a 0, I would like to display the latest available value from the FACT table.&nbsp;&nbsp;Please also see the screenshots attached.Happy about any kind of suggestions or tips! 🙂

harikm007 · Answer

jmn3&nbsp;Check below script if it gives you expected result
widget.on('processresult', function(se, ev){
	
	var prevValue = -1
	
	$.each(ev.result.series[0].data, function(index, value){

series0 = ev.result.series[0].data
		series1 = ev.result.series[1].data

if(index == 0)
		{
			prevValue = series0[index].y + series1[index].y
		}
		else
		{
			if((!series0[index].y || series0[index].y == 0) &amp;&amp; (!series1[index].y || series1[index].y == 0))
			{
				ev.result.series[1].data[index].y = prevValue
			}
			else
			{
				prevValue =series0[index].y + series1[index].y
			}
		}

})
})

-Hari

harikm007 · Answer

Anonymous&nbsp;
Please check if this script works for you
widget.on('processresult', function(se, ev){
	
	$.each(ev.result.series, function(index, value){
		
		var prevValue = -1
		
		$.each(value.data, function(dataindex, datavalue){

if(dataindex == 0)
				prevValue = datavalue.y
			else
			{
				if(!datavalue.y)
					datavalue.y = prevValue
				else
					prevValue = datavalue.y
			}	
		})
	})
})
-Hari

anonymous · Answer

Hi&nbsp;harikm007&nbsp;Thank you for providing this script.I have just tested it and it seems to work "partly". All widgets have a time filter applied for 21.02.22-27.02.22.As you can see in the screenshot attached, I think it works as long as there is data for at least one of the two products displayed. If I only display the stock level for one product (see widget Nr. 3), then the script does not seem to work anymore. This is also indicated in widget Nr.2 where the script is applied: There are no values displayed for 26th and 27th February, as there is no value in the FACT table for both products.&nbsp; On 24.02. and 25.02 it correctly takes the value of 57 from 23.02.&nbsp;Is there something we can change on the script to always apply this logic?Thanks a lot!Yannick&nbsp;&nbsp;

anonymous · Answer

Also, I noticed the following: It does only seem to work for the days after the start date.
There is a 0 displayed for the product "the adventurer" on 21.02.22, however, there are values from previous days available.

harikm007 · Answer

Anonymous&nbsp;,
This script simply fill the blank bars with previous value and it will not fetch any additional data from table.
When you filter to one product, I think widget shows only 2 bars (without script) and there is no blank bar. If there is any empty space with date as in the below screenshot, script will update the space with previous value.

-Hari
&nbsp;

Forum Discussion

Display values continuously over time with existing gaps in FACT data table

10 Replies

Recent Discussions

Pivot Table widget - column headers and column data out of alignment

Blox conditional formatting up and down indicators

Remove Widget Title Box

Change Legend Category Names

Rename the Pivot Grand Total

Sisense

Support

Resources