Highlighted
Community Boss

Formula issue - Add value from previous months

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,

 

 

9 REPLIES 9
Highlighted
Certified Master Anaplanner

Re: Formula issue - Add value from previous months

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

Highlighted
Community Boss

Re: Formula issue - Add value from previous months

Hi Amaya,

 

Appreciate the response and sorry about the confusing explanation.

 

Regards,

Highlighted
Master Anaplanner/Community Boss

Re: Formula issue - Add value from previous months

@Jsdeloria21 

I agree with @Amaya. Cumulate seems to be the best option based on your description.

Additionally, you might also be able to use the isfirstoccurence() function too, as this might be better if your use-case is nuanced.

 

Cumulate001.png


Jared Dolich
Highlighted
Certified Master Anaplanner

Re: Formula issue - Add value from previous months

I agree with the above Cumulate is your best bet if you are always summing from the beginning of the time range. Here a link to the Anapedia page which will guide you how to use it: 

 

 https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/CUMULATE.html  

Community Boss

Re: Formula issue - Add value from previous months

Hi Jared,

 

Appreciate the response. So the formula of your Target Sales Cumulate is Cumulate(Sales) i just need to add the identifier as a condition?

 

Regards,

Highlighted
Master Anaplanner/Community Boss

Re: Formula issue - Add value from previous months

@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.


Jared Dolich
Highlighted
Contributor

Re: Formula issue - Add value from previous months

@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-s...

 

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

 

Thanks,

Sandeep

 

 

Highlighted
Master Anaplanner/Community Boss

Re: Formula issue - Add value from previous months

@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


Jared Dolich
Highlighted
Frequent Contributor

Re: Formula issue - Add value from previous months

 

@Jared Dolich 

 

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