topic Add Cumulative Sum variable during load in QlikView

Add Cumulative Sum variable during load

Antoine5531 — Mon, 02 Aug 2021 12:14:08 GMT

Hello,

I'm trying to add two variables to my data in the load.

- The first one should sum the amounts of all the records of the same year and categories.

- The second one should sum the amounts of all the records of the same year, categories for this month + the previous ones.

Examples :

ID	Year	Month	cat	Amount	Var1	Var2
10	2021	01	a	10	40	10
9	2021	02	a	10	40	20
8	2021	03	a	10	40	40
7	2021	03	a	10	40	40
6	2021	01	b	11	55	22
5	2021	01	b	11	55	22
4	2021	02	b	11	55	55
3	2021	02	b	11	55	55
2	2021	02	b	11	55	55
1	2020	01	a	5	5	5

I got a part of my answer in this post (https://community.qlik.com/t5/QlikView-App-Dev/Cumulative-sum-by-month-in-load/m-p/1825419) but have a hard time to get the right result with my data and am stuck since several days.

I join you a file with a sample of my data.

Thanks a lot

Re: Add Cumulative Sum variable during load

Kushal_Chawda — Mon, 02 Aug 2021 10:27:20 GMT

@Antoine5531 you have duplicate data?

2021	03	a	10	40	40
2021	03	a	10	40	40

2021	01	b	11	55	22
2021	01	b	11	55	22
2021	02	b	11	55	55
2021	02	b	11	55	55

Re: Add Cumulative Sum variable during load

Antoine5531 — Mon, 02 Aug 2021 12:14:34 GMT

No, i forgot to add an id to the example you're right but i don't have any duplicate data in my original dataset.

I corrected the first message + file.

Re: Add Cumulative Sum variable during load

Kushal_Chawda — Mon, 02 Aug 2021 13:51:08 GMT

@Antoine5531 how the var2 values is 22 for below record? Shouldn't be 11? Also, you need to sort the data correctly as you are having same values for different IDs. Does your ID field contains Numeric values? Can I use ID to sort your data?

2021

Re: Add Cumulative Sum variable during load

Antoine5531 — Mon, 02 Aug 2021 14:01:19 GMT

The rows 5 and 6 are both at the month '01' and in the same categorie. var2 sum every amount of the year of the same categorie until the current month (+ current month). Since we are in january it does equal 22.

Sorting by ID wouldn't work with my dataset as some values are added one month late. However, i can create a field date with the year and month to sort them.

Re: Add Cumulative Sum variable during load

Kushal_Chawda — Mon, 02 Aug 2021 15:22:59 GMT

@Antoine5531 try below

Data: LOAD ID, Year, Month, cat, Amount FROM Table; Left Join(Data) LOAD Year, cat, sum(Amount) as Var1 Resident Data Group by Year,cat; Final: LOAD *, if(RowNo()=1,1, if((Year=Previous(Year) and cat=Previous(cat) and Month<>Previous(Month)) or (Year=Previous(Year) and cat<>Previous(cat)) or Year<>Previous(Year), rangesum(Peek('Group'),1),Peek('Group'))) as Group, if((Year=Previous(Year) and cat<>Previous(cat)) or Year<>Previous(Year),Amount, rangesum(Peek('Accum'),Amount)) as Accum Resident Data Order by Year,cat,Month; DROP Table Data; Left Join(Final) LOAD Group, max(Accum) as var2 Resident Final Group by Group; DROP Fields Group,Accum;

Re: Add Cumulative Sum variable during load

Antoine5531 — Tue, 03 Aug 2021 07:16:24 GMT

I'll try that tomorow morning and keep you updated. Thanks a lot

It work. Thanks again