Load cumulativ data

[Øystein_Kolsrud]

I often have cases where I want to analyze cumulative data across a table. A typical example is that I want to display cumulative quarterly sales per year when my input data contains data regarding sales per quarter. So I would like to take this input:

Year	Quarter	Sales
2020	1	1
2020	2	2
2020	3	7
2020	4	2
2021	1	8
2021	2	1
2021	3	1
2021	4	3

And get this table:

Year	Quarter	Sales	Cumulative Sales
2020	1	1	1
2020	2	2	3
2020	3	7	10
2020	4	2	12
2021	1	8	8
2021	2	1	9
2021	3	1	10
2021	4	3	13

What is the best way to do this in the script?

[MarcoWedel · Accepted solution]

one solution might be:

tabSales:
LOAD Year, 
     Quarter, 
     Sales,
     RangeSum(Sales,If(Year=Previous(Year),Peek(CumulativeSales))) as CumulativeSales
FROM [https://community.qlik.com/t5/App-Development/Load-cumulativ-data/m-p/1863624] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

Marco

[Øystein_Kolsrud]

Nice! Thank you very much for the solution! I actually didn't realize I could use Peek and refer to a field that doesn't exist. I would assume it would return a "Field 'CumulativeSales' not found" error on the first row, but I see now that the Peek function returns 'null' whenever a field is non-existent.