cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

Yannick
8 - Cloud Apps
8 - Cloud Apps

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! ๐Ÿ™‚

display missing values as 0.png

stacked column chart.png

widget JAQL.png

โ€ƒโ€ƒโ€ƒ





5 REPLIES 5

harikm007
12 - Data Integration
12 - Data Integration

@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

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
8 - Cloud Apps
8 - Cloud Apps

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
12 - Data Integration
12 - Data Integration

@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.

harikm007_0-1646663392101.png

-Hari

 

Yannick
8 - Cloud Apps
8 - Cloud Apps

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!

Voice of Customer
Impact Business Intelligence (BI) and Analytics Market Research

Click Here & Complete Dresner's Wisdom of Crowdsยฎ Market Study Survey Today!

Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request

Don't miss out:

Earn a BETA Launch Challenge Badge!