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.

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

Regards,

Accepted answers

Amaya

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

JaredDolich

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

SandeepB

@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

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

Akhtar.shahbaz

@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

All comments

Amaya

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

JaredDolich

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

AWhitworth

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

Jsdeloria21

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,

JaredDolich

@Jsdeloria21

That is correct.

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

Jsdeloria21

Hi Amaya,

Appreciate the response and sorry about the confusing explanation.

Regards,

SandeepB

@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

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

Akhtar.shahbaz

@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

Thanks

Akhtar

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…