Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Antoine5531
Contributor II
Contributor II

Cumulative sum by month in load

Hello,

I'm trying to add a variable to my data that would sum the values of all the previous month of this year + the actual month.

Here is an example :

YearMonthidAmountNew variable
202101a1010
202102b1020
202103c1050
202103d1050
202103e1050
202104f1060
202105g1080
202105h1080
202106i1090

 

I manage to do it in a table but i'm struggling to do it in the script pls help.

Thanks.

 

Labels (2)
1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like below

Test1:
LOAD * INLINE [
Year, Month, id, Amount
2021, 01, a, 10
2021, 02, b, 10
2021, 03, c, 10
2021, 03, d, 10
2021, 03, e, 10
2021, 04, f, 10
2021, 05, g, 10
2021, 05, h, 10
2021, 06, i, 10
];


Join
Load *, SumAmount+Alt(Peek('NewVariable'),0) as NewVariable;
Load Year,Month, Sum(Amount) as SumAmount Resident Test1 group by Year, Month;

Thanks & Regards,
Mayil Vahanan R

View solution in original post

2 Replies
MayilVahanan

Hi

Try like below

Test1:
LOAD * INLINE [
Year, Month, id, Amount
2021, 01, a, 10
2021, 02, b, 10
2021, 03, c, 10
2021, 03, d, 10
2021, 03, e, 10
2021, 04, f, 10
2021, 05, g, 10
2021, 05, h, 10
2021, 06, i, 10
];


Join
Load *, SumAmount+Alt(Peek('NewVariable'),0) as NewVariable;
Load Year,Month, Sum(Amount) as SumAmount Resident Test1 group by Year, Month;

Thanks & Regards,
Mayil Vahanan R

View solution in original post

aditya12345
Contributor
Contributor

You can use the below - 

LOAD Year,
Month,
id,
Amount,
if(Year = previous(Year), Rangesum(Amount,peek([New variable])), Amount) as [New variable]
FROM
[<PATH>]
(ooxml, embedded labels, table is Sheet1);