How to vary the calculation used for each version for a single line item?

OliverMunns
Occasional Contributor

How to vary the calculation used for each version for a single line item?

I have a large model that is used to generate a business forecast. Each time we need to generate a new forecast or scenario, a new version is added so that the inputs can be changed and the outputs then compared to previous versions.

 

However, sometimes the calculations themselves need to change to reflect our evolving business model. When this happens, we don't want to change the results of previous versions, only new versions.

 

I am familiar with the method of using IF statements, combined with bools to identify versions, to list different calculations for different versions in one long statement. However, as time goes on and with many line items affected, this becomes very untidy and difficult to track.

 

Is there any other easier/more scalable way to apply a different calculation for different versions on the same line item?

8 REPLIES 8
Akhtar.shahbaz
Community Boss

Re: How to vary the calculation used for each version for a single line item?

Hi @OliverMunns ,

 

You can refer to the below link, this will give you some idea for optimizing the formula.

 

https://community.anaplan.com/t5/How-To/Variance-Analysis-With-Native-Versions-Made-Easy/ta-p/98336

OliverMunns
Occasional Contributor

Re: How to vary the calculation used for each version for a single line item?

Hi @Akhtar.shahbaz,

 

I've read through the article. This looks useful for addressing variance analysis but I don't see how this could be applied to the problem that I raised, regarding applying different calculations to the same line item for different versions?

ChrisHeathcote
Community Boss

Re: How to vary the calculation used for each version for a single line item?

@OliverMunns 

You will need to use a series of nested IF THEN ELSE queries to direct the calculation to run a specific formula for each version. Use a systems module of versions to cycle through each iteration of version using a boolean line item each time to indicate which version is being queried. 

Example - 

=IF VersionModule.Forecast THEN X ELSE IF VersionModule.Budget THEN Y ELSE IF VersionModule.Actual THEN Y ELSE 0

For each IF THEN ELSE you are using a boolean in a systems module to tell Anaplan that for that version(s) run the calculation after the THEN otherwise continue to the next query.

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
rob_marshall
Moderator

Re: How to vary the calculation used for each version for a single line item?

@OliverMunns @ChrisHeathcote 

 

I would definitely shy away from the nested IF statements for the following reasons:

  • performance
  • scalability
  • maintenance
  • enablement.

@OliverMunns if you are using Native Versions (out of the box Versions), have you considered Version Formula?

 

https://help.anaplan.com/10c7423e-7824-4309-a192-93c6c57d3b63-Add-Version-Formula-

 

In using Version formula, you will need to create a line item subset (LISS), but this will get you home AND get you away from the nasty nested IF THEN ELSE statements.

 

Rob

 

 

ChrisHeathcote
Community Boss

Re: How to vary the calculation used for each version for a single line item?

@rob_marshall 

Thanks for nudging me in this direction.

I have not used Version Formula before and it really is a great piece of functionality.

Nested IF THEN ELSE are horrid but I didnt realise there was an alternative.

 

Thanks,

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
rob_marshall
Moderator

Re: How to vary the calculation used for each version for a single line item?

@ChrisHeathcote 

 

It really is a cool piece of functionality that not a lot of people know about.  You should know this by now, I have your back!

OliverMunns
Occasional Contributor

Re: How to vary the calculation used for each version for a single line item?

Hi @rob_marshall,

 

I've had a look into version formula. It potentially looks promising but am I reading correctly that a single formula will apply to all of the line items in the LISS that you create? If so, how would this work where all line items have their own (already complicated) formula?

 

Do you know if there are any example models where this is demonstrated at a reasonable scale?

 

Thanks.

rob_marshall
Moderator

Re: How to vary the calculation used for each version for a single line item?

@OliverMunns 

 

Nothing is stated that you can't have multiple LISS's defined.  But in reality, I guess it depends on your calcs and what you are attempting to accomplish.

 

Rob