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

[Yannick]

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 = 5
02.01.22: stock level = 4
06.01.22: stock level = 10

When 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.  
Please also see the screenshots attached.

Happy about any kind of suggestions or tips! 🙂

   

[harikm007]

@Yannick 

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

[Yannick]

Hi @harikm007 
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.  On 24.02. and 25.02 it correctly takes the value of 57 from 23.02. 

Is there something we can change on the script to always apply this logic?
Thanks a lot!

Yannick  

[Yannick]

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]

@Yannick ,

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

[jmn3]

@harikm007 do you know how I could modify this script if I have multiple stacked values? Basically go from this:

to this:

[harikm007]

@jmn3 Can you share bit more information about how the stacked bars should flow from left to right, if few values are blank? In above example, should '5/30/23' have both dark blue and orange bars as '5/23/23' has both colors?

-Hari

[jmn3]

@harikm007 thank you for your response.

The orange bar represents new counts added that week and the blue bar is a running count of previous weeks. The weeks that have no values should be blue and have the total count of the last weeks with values (aka new + running sum). 

[harikm007]

@jmn3 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) && (!series1[index].y || series1[index].y == 0))
			{
				ev.result.series[1].data[index].y = prevValue
			}
			else
			{
				prevValue =series0[index].y + series1[index].y
			}
		}	

	})
})

-Hari

[jmn3]

@harikm007 this works. Thank you!

[Yannick]

HI @harikm007 
Ok, thanks, got it. I think I need to find a different solution then, as I basically need to fill all gaps from the data side and not just the ones shown between the columns of the widget. 
But thanks a lot for your suggestion!