Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
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

Labels (6)

• ### Visualization

1 Solution

Accepted Solutions
Champion III

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

(Month, (NUMERIC, ASCENDING))))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
11 Replies
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.
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

Champion III

ANy filters would be in the outer sum()

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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
];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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.

Thanks

Hari

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

Champion III

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

exit Script;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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.

Champion III

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