Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Antoine5531
Contributor II
Contributor II

Add Cumulative Sum variable during load

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 :

IDYearMonthcatAmountVar1Var2
10202101a104010
9202102a104020
8202103a104040
7202103a104040
6202101b115522
5202101b115522
4202102b115555
3202102b115555
2202102b115555
1202001a555

 

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

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

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

 

Kushal_Chawda_0-1627917767461.png

 

View solution in original post

6 Replies
Kushal_Chawda

@Antoine5531  you have duplicate data?

202103a104040
202103a104040

 

202101b115522
202101b115522
202102b115555
202102b115555
Antoine5531
Contributor II
Contributor II
Author

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?

6202101b115522
Antoine5531
Contributor II
Contributor II
Author

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

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

 

Kushal_Chawda_0-1627917767461.png

 

View solution in original post

Antoine5531
Contributor II
Contributor II
Author

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

It work. Thanks again