Dynamic Aggregation

xzhan252
Occasional Contributor

Dynamic Aggregation

Hi,

 

I have a module below, a list (transaction date) on the rows and time period on the columns. Is there a way to do dynamic aggregation vertically based the list transaction date? For example, I want to have this cell (time period: Apr 18, list period: Aug 19) to show sum of transaction period Jan 19 - Jul 19 for time period  Apr 18. 

 

I know MOVINGSUM() works horizontally for TIME, is there a way to make it works for the list?

xzhan252_0-1628808951988.png

 

Thanks

Jayden

 

1 ACCEPTED SOLUTION

Accepted Solutions
anikdas
Certified Master Anaplanner

Re: Dynamic Aggregation

Hi Jayden,

You can achieve this by using cumulate over lists. Formula to use for that:

CUMULATE(Source Data, FALSE, "List against which Cumulate to be done")

This will sum the source data in the order of the list. In your case that list is the transaction date.

Example below:

Source Data:

anikdas_0-1628839583022.png

Calc Line item:

anikdas_1-1628839612651.png

If you need the aggregation to be for specific month span, you can create a mapping module and use that to subtract between two points of the cumulate.

Note: The module that you have shown looks very sparse.

Thanks
Anik

View solution in original post

3 REPLIES 3
anikdas
Certified Master Anaplanner

Re: Dynamic Aggregation

Hi Jayden,

You can achieve this by using cumulate over lists. Formula to use for that:

CUMULATE(Source Data, FALSE, "List against which Cumulate to be done")

This will sum the source data in the order of the list. In your case that list is the transaction date.

Example below:

Source Data:

anikdas_0-1628839583022.png

Calc Line item:

anikdas_1-1628839612651.png

If you need the aggregation to be for specific month span, you can create a mapping module and use that to subtract between two points of the cumulate.

Note: The module that you have shown looks very sparse.

Thanks
Anik

View solution in original post

xzhan252
Occasional Contributor

Re: Dynamic Aggregation

Thanks, Anik. Is there a way to do cumulate just the rows above? For example, I want to SUM Jan through Apr and show that cumulative value in May, June will show cumulative value from Jan through May, etc

 

Thanks

Jayden 

anikdas
Certified Master Anaplanner

Re: Dynamic Aggregation

Hi Jayden,

You can create a mapping module to capture the time period you want to sum to. E.g. For June that module need to have one line item pointing to May. Then use a lookup on the cumulate line item using the mapping to get the desired amount.

Thanks
Anik