topic Re: Script: cumulative sum + group by in QlikView

Script: cumulative sum + group by

twanqlik — Tue, 25 May 2021 16:02:13 GMT

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? 🙂

Re: Script: cumulative sum + group by

chrismarlow — Tue, 25 May 2021 19:22:39 GMT

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.

Re: Script: cumulative sum + group by

MAPSF1 — Wed, 01 Dec 2021 11:21:44 GMT

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?

Re: Script: cumulative sum + group by

chrismarlow — Wed, 01 Dec 2021 11:30:37 GMT

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.

Re: Script: cumulative sum + group by

MAPSF1 — Wed, 01 Dec 2021 11:45:47 GMT

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