topic Re: Load cumulativ data in App Development

Load cumulativ data

Øystein_Kolsrud — Thu, 25 Nov 2021 16:13:57 GMT

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?

Re: Load cumulativ data

MarcoWedel — Thu, 25 Nov 2021 20:51:36 GMT

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

Re: Load cumulativ data

Øystein_Kolsrud — Fri, 26 Nov 2021 07:35:18 GMT

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.