Add Cumulative Sum variable during load

[Antoine5531]

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

[Kushal_Chawda]

@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

[Antoine5531]

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.

[Kushal_Chawda]

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

6

2021

01

b

11

55

22

[Antoine5531]

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.

[Kushal_Chawda · Accepted solution]

@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;

[Antoine5531]

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

It work. Thanks again