cancel
Showing results for 
Search instead for 
Did you mean: 

RSUM with Display Missing as Zero

wallingfordce
10 - ETL
10 - ETL

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

wallingfordce_0-1668564755888.png

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.

wallingfordce_1-1668565250014.png

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

2 ACCEPTED SOLUTIONS

Harry
9 - Travel Pro
9 - Travel Pro

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

QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

 

View solution in original post

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

 

View solution in original post

3 REPLIES 3

Harry
9 - Travel Pro
9 - Travel Pro

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

QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

 

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!

 

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