Applying Date Filter after calulating cumulative

[h_prakash]

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

[vinieme12]

as below

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

[h_prakash]

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]

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))

[vinieme12]

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

[h_prakash]

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]

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]

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

[h_prakash]

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 · Accepted solution]

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))))