Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
h_prakash
Creator II
Creator II

Applying Date Filter after calulating cumulative

Hi Experts,

I have a flowing requirement where I need to show last three months data after calculating cumulative.

Data is like this and I am looking for from end soultion.

I have calculated cumulative amount using Rangesum(above(total Sum( Amount) , 0, RowNo(Total))).

Date Amount Cumulative Amount
Jan-21 10 10
Feb-21 20 30
Mar-21 30 60
Apr-21 40 100
May-21 50 150
Jun-21 60 210
Jul-21 70 280
Aug-21 80 360
Sep-21 90 450
Oct-21 100 550
Nov-21 110 660
Dec-21 120 780
Jan-22 130 910
Feb-22 140 1050
Mar-22 150 1200
Apr-22 160 1360

 

and I want Data to look like 

Date Amount Cumulative Amount
Jan-22 130 910
Feb-22 140 1050
Mar-22 150 1200
Apr-22 160 1360

 

Any help is appreciated.

 

Thanks

Hari

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

You are right, here is the updated expression with a sorted Agg()

 

Sum({<Month={">$(=date(addmonths(max({<Month=>}Month),-3)))"}>}aggr(rangesum(above(count({<Month=>}distinct [Account Number]),0,rowno(total))),
(Month, (NUMERIC, ASCENDING))))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

11 Replies
vinieme12
Champion III
Champion III

as below

= sum(Aggr({<Date=>}rangesum(above(sum({<Date=>}Amount),0,rowno(total))),Date))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
h_prakash
Creator II
Creator II
Author

Hi Vineeth,

Thank you for responding. I dont see the Date filter where I need to show latest three months (Current - three) after calculating cumulative.

 

Thanks

Hari

vinieme12
Champion III
Champion III

ANy filters would be in the outer sum() 

=sum({<Date={">=$(=Date(addmonths(Max(Date),-3)))<=$(=Date(Max(Date)))"}>}Aggr(rangesum(above(sum({<Date=>}Amount),0,rowno(total))),Date))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

example, your date must be actual dates;

raw:
Load monthstart(date#(Date,'MMM-YY')) as Date,  Date as MonthNames   ,Amount,CumulativeAmount inline [
Date,Amount,CumulativeAmount
Jan-21,10,10
Feb-21,20,30
Mar-21,30,60
Apr-21,40,100
May-21,50,150
Jun-21,60,210
Jul-21,70,280
Aug-21,80,360
Sep-21,90,450
Oct-21,100,550
Nov-21,110,660
Dec-21,120,780
Jan-22,130,910
Feb-22,140,1050
Mar-22,150,1200
Apr-22,160,1360
];

qlikCommunity1.PNG

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
h_prakash
Creator II
Creator II
Author

Hi Vineeth,

It is working with the sample data but I am not sure why it is not working with the this data. Attaching the QVW file. 

I really appreciate your response.

 

Thanks

Hari

h_prakash
Creator II
Creator II
Author

Hi @vinieme12 ,

Please find the sample excel file. 

With this sample data I am able to do Cumulative like this.

Month Account Number Count Cumulative Account Number Count
04/30/2020 6059 6059
05/31/2020 68677 74736
06/30/2020 13034 87770
07/31/2020 9185 96955
08/31/2020 5229 102184
10/31/2020 25 102209
01/31/2021 1 102210
02/28/2021 44506 146716
03/31/2021 46561 193277
04/30/2021 63278 256555
05/31/2021 36500 293055
06/30/2021 28059 321114
07/31/2021 3481 324595
08/31/2021 18 324613
09/30/2021 21345 345958
10/31/2021 2 345960
11/30/2021 1 345961
12/31/2021 12253 358214

 

But I am not able to restrict last three months after doing cumulative count. like this

Month Last three Months Cumulative Account Number Count
10/31/2021 345960
11/30/2021 345961
12/31/2021 358214

 

Thanks

Hari

vinieme12
Champion III
Champion III


raw:
LOAD
"Account Number",
"Month"
FROM [lib://AttachedFiles/Sample File.xlsx]
(ooxml, embedded labels, table is Sheet1);


exit Script;

 

Sum({<Month={">$(=date(addmonths(max({<Month=>}Month),-3)))"}>}aggr(rangesum(above(count({<Month=>}distinct [Account Number]),0,rowno(total))),Month))z

 

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
h_prakash
Creator II
Creator II
Author

Hi Vineeth,

 

Thank you for response. But if you look the cumulative values vs. the values when applied month filter the values are different. 

vinieme12
Champion III
Champion III

You are right, here is the updated expression with a sorted Agg()

 

Sum({<Month={">$(=date(addmonths(max({<Month=>}Month),-3)))"}>}aggr(rangesum(above(count({<Month=>}distinct [Account Number]),0,rowno(total))),
(Month, (NUMERIC, ASCENDING))))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.