Movingsum Function Workaround

innocent
innocent
in Modeling

Hi Anaplaners,

I'm have 2 line items
Margin and Moving sum

doing a MOVINGSUM on a line item moving sum. And I would like to capture in my line item where movingsum is done.

I would like to calculate MoM Average between 2021 and 2022. And When MoM margins differ by more than 30% - I will replace the value with the margin percentage for the same month a year ago.

Any Idea how to workaround this?.

image.png

Best Answer

  • AlejandroGomez
    AlejandroGomez
    Answer ✓

    Hi @innocent

    It seems you are almost there. If I have understood correctly your request, all you need to do is to add an IF statement in your "Moving Sum" line item. Something like this:

    IF 'Margin' > 0.3 then Lag('Margin percentage',12,0) else MOVINGSUM(Margin, -12, -1, Average)

    I hope this puts you on the right track.

    Cheers. Alex

Answers

Categories