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

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.