RSUM with Display Missing as Zero

[wallingfordce]

When using the feature to Display Missing Values as Zeros, the results are what I would expect for a simple SUM. However, for an RSUM the results should fold the zeros into the value within RSUM and not return zeros for the RSUM itself.

In this image, the top widget shows the SUM (blue) and RSUM (purple) values without Display Missing as Zero. The lower image shows them with Display Missing as Zero, and includes what I'd expect to see (dotted red). 

I've attached the Sample ECommerce dashboard as well (community security doesn't allow .dash attachments, so renamed .txt). One thing I'd like to avoid is using a widget script that draws a line connecting the two points on either side of a NULL gap. These solutions typically draw a diagonal line which imply growth within the NULL gap, rather than a flat line across the gap.

Is there a frontend or widget-script way around this?

[Harry · Accepted solution]

Hello @wallingfordce ,

Try this widget script. (update the variable 'panelName' with name of panel with RSUM calculation)

widget.on('processresult', function(se, ev){
	
	var panelName = 'RSUM(Total Quantity (1))'
	
	var series = ev.result.series.find(el => el.name == panelName)
	var prevValue = series.data[0].y
	
	$.each(series.data, function(index, value){
		if(value.y == null)
			value.y = prevValue
		else
			prevValue = value.y	
	})
	
})

Please let me know if you have any questions

Always here to help,
Harry from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

[Harry · Accepted solution]

Hello @wallingfordce ,

Try this widget script. (update the variable 'panelName' with name of panel with RSUM calculation)

widget.on('processresult', function(se, ev){
	
	var panelName = 'RSUM(Total Quantity (1))'
	
	var series = ev.result.series.find(el => el.name == panelName)
	var prevValue = series.data[0].y
	
	$.each(series.data, function(index, value){
		if(value.y == null)
			value.y = prevValue
		else
			prevValue = value.y	
	})
	
})

Please let me know if you have any questions

Always here to help,
Harry from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

[wallingfordce]

This is great Harry. I really appreciate it.

Soooo, I didn't know at the time, that the use case includes a field on the break by panel. It doesn't seem like this will support that. Do I need another breakBy.data and $.each? 

Is this the best resource to learn more about widget scripting?
Widget Class | Sisense Developers

Thanks!

[harikm007 · Accepted solution]

When there is a 'break by', we need to add $.each on Series as below:

widget.on('processresult', function(se, ev){
	
	$.each(ev.result.series, function(seriesIndex, seriesValue){
		
		var prevValue = seriesValue.data[0].y

		$.each(seriesValue.data, function(index, value){
			if(value.y == null)
				value.y = prevValue
			else
				prevValue = value.y	
		})
	
	})	

})

-Hari