Script: cumulative sum + group by

[twanqlik]

Date	Market	Factor	Value	Cumulative per Date and Market
March	NAM	1	50	50
March	NAM	3	25	75
March	NAM	12	10	85
March	AFI	5	200	200
March	AFI	9	100	300
March	AFI	1	75	375
April	NAM	10	500	500
April	NAM	11	250	750
April	NAM	3	150	900
April	AFI	8	90	90
April	AFI	3	30	120
April	AFI	1	10	130

I'm trying to calculate the field: Cumulative per Date and Market.

I tried with the following line, but it doesn't work: 

if(Market = Previous(Market), RangeSum(Value, Peek('Pareto_Negative_Cumulative')), Value) AS Pareto_Negative_Cumulative,

Order by Date, Value,Market desc;

Any ideas? 🙂

[chrismarlow · Accepted solution]

Hi,

I think you need to use Peek and will have to test on the first row, so try;

data:
Load
	If(RowNo()=1,
		Value,
		If(Date=Peek(Date) and Market=Peek(Market),
			Peek([Cumulative per Date and Market])+Value,
			Value
			)
		)  AS [Cumulative per Date and Market],
	*;
LOAD * Inline [
Date,	Market,	Factor,	Value
March,	NAM,	1,	50	
March,	NAM,	3,	25	
March,	NAM,	12,	10	
March,	AFI,	5,	200	
March,	AFI,	9,	100	
March,	AFI,	1,	75	
April,	NAM,	10,	500	
April,	NAM,	11,	250	
April,	NAM,	3,	150	
April,	AFI,	8,	90	
April,	AFI,	3,	30	
April,	AFI,	1,	10	
];

Cheers,

Chris.

[MAPSF1]

Is there a way out using this script, without the INLINE statement? What if the data is a much larger set coming directly from source? 

[chrismarlow]

Hi,

The first load statement can act as a preceding load to either data loaded previously or an external load, the INLINE is just an example based off what you provided.

What is important is that your source data is sorted correctly, so the INLINE was already.

This will add to the load time, so if running across lots of data that may become a problem. It is possible to do cumulative stuff in charts ... so if this is just one bit of the application and not used a lot you could take the performance hit there, you would need to experiment.

Cheers,

Chris.

[MAPSF1]

thanks for your reply. I think maybe the sorting is proving to be difficulty, but also, the cumulative sum as only the most recent record is being included when I use your above code. 

My problem is further outlined here; https://community.qlik.com/t5/New-to-Qlik-Sense/Cumulative-sum-in-script/td-p/1863987