Dynamic Aggregation

xzhan252
xzhan252
in Modeling

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

 

Tagged:

Best Answer

  • anikdas
    anikdas
    Answer ✓

    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.

Answers

  • xzhan252
    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
    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.

Categories