Dynamic Aggregation

[xzhan252]

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?

Thanks

Jayden

[anikdas · Accepted solution]

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:

Calc Line item:

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.

[xzhan252]

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]

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.