Formula issue - Add value from previous months

Jsdeloria21
Jsdeloria21
in Modeling

Hi Guys,

 

I have been working on this formula for quite a while now, I hope you can give me an idea on how to do this. I will explain it as much as I can since I cannot provide a screenshot of the actual report from the client.

 

I have 2 tables with Line Item and Time Dimension. "Value 1" row has the value, "Identifier" row , and "Target Result" row is the target module. 

 

so the formula is that IF Identifier is greater than 1 then I need to add the values from previous months. In the Target Result row, May 2013 has the value of 50 because the condition should add values from Jan 13 until Apr 13 which results to 50. Same thing with Jun 13 since Identifier is 1 it will add values from Jan 13 to May 13 and so on. It means the formula is moving as the months progress.

 

Jsdeloria21_1-1580995699845.png

 

I hope you can give me an idea on the approach as to how to do this.

 

Regards,

 

 

Tagged:

Best Answers

  • Amaya
    Amaya
    Answer ✓

    Sorry if I don't understand what you want to do.
    How about this? It looks like yours.

     

    IF Identifier THEN CUMULATE('Value 1') - 'Value 1' ELSE 0

     

    Cumulate.png

  • JaredDolich
    JaredDolich
    Answer ✓

    @Jsdeloria21 

    That is correct.

    For best practices, whenever you use a conditional you always want to have the most likely outcome first. So, if you anticipate the "1" will show up early in the year, then set your IF statement to check for the 1 first. This allows Anaplan to stop processing the IF statement early once it become satisifed.

     

    Depending on what conditions your using to calculate a 1 or 0 you might even consider putting that in the IF statement. That way you won't even need the "identifier" line item.

     

    If it's a complicated identifier, but only uses a subset of the dimensions in your module, then you'll want to create a Boolean in a system module. this is a little advanced but its how we optimize modules for performance.

     

    If you have more details about what you're doing, I'm sure we'd all be happy to help you get you the most efficient formula possible.

     

    Good luck. Anxious to see what you decide to do.

  • sandeep_bk
    sandeep_bk
    Answer ✓

    @Jsdeloria21 ,

     

    Looks like this post is similar to the previous one you had asked. 

     

    https://community.anaplan.com/t5/Anaplan-Platform-Discussions/Formula-problem-adding-values-in-the-same-line-item/m-p/59511#M10465

     

    I wonder, if both yield the same result, Which one would perform well?

     

    Thanks,

    Sandeep

     

     

  • JaredDolich
    JaredDolich
    Answer ✓

    @Jsdeloria21 

    Thank you @sandeep_bk for reminding us of that post!

    @rob_marshall had a very good suggestion that if you can solve the problem with PREVIOUS it would be better since CUMULATE spans all the time periods all the time.

    @AlejandroGomez had the same suggestion and solved it.

    I've added the PREVIOUS scenario to this module to show the difference. It's using only PREVIOUS. It yields the same result but I did have to add a line item to accumulate the sales.

    Cumulate002.pngCumulate003.png

  • Akhtar.shahbaz
    Akhtar.shahbaz
    Answer ✓

     

    @JaredDolich 

     

    I agree with you,we can get same result either by using PREVIOUS or CUMULATE but it depends on the Timescale of the model.

    @sandeep_bk 

    As mentioned in the PLANUAL rule no (2.02-10 ), " For long timescales, using PREVIOUS is faster than CUMULATE due to the number of "reads" required for the calculation"
    and where the number of periods is small, Cumulate is faster. 

     

    Thanks

    Akhtar

     

Answers

Categories