Reducing calculation effort

ChrisMcCarthy

Currently looking to make use of the feature where can now see the calculation effort entailed in certain line items and I've already been able to eliminate or refine a number of elements in various modules but just wanted to check in terms of best approach to take to this next one.

I have included a copy of the formula for reference in attachment

calculation effort.pdf

Uploaded Apr 4, 202567.36 KB

Plan of attack was to do the following

1. Ensure IF statements are ordered so as to allow the quickest exit, thereby meaning able to exit formula most effectively
2. Examine number of SUM functions that are in formula

It is this second element that looking for advice on, what would be the best approach on this? Any advice would be greatly appreciated on this.

Thinking that changing the "Summary" option from the current setting in the calculation module

As the values from this module are then taken to other modules, which they are then reviewed by users at various levels in these output modules, just wanted to check whether this is correct approach

andrewtye

Hi @ChrisMcCarthy - would probably have a look at this:

Nested Ifs | Model Optimization Team series — Anaplan Community

and also pull out those huge calculations into another module, it should make it easier on the model

Andrew

rob_marshall

@ChrisMcCarthy

First thing I would do is create a CALC module doing the sum's for INP08 Template Spend as you are currently doing the "same" sum (same aggregations) four times:

• 'INP08 Template Spend'.MEGA GBP Constant[SUM: 'INP08 Template Spend'.Cost Centre,SUM: 'INP08 Template Spend'.GL Category]
• 'INP08 Template Spend'.'H3 GBP Constant'[SUM: 'INP08 Template Spend'.Cost Centre,SUM: 'INP08 Template Spend'.GL Category]
• 'INP08 Template Spend'.GBP Constant Excl Projects[SUM: 'INP08 Template Spend'.CostCentre, SUM: 'INP08 Template Spend'.GL Category]
• 'INP08B Template Spend_COS'.Value GBP Constant[SUM: 'INP08B Template Spend_COS'.Cost Centre, SUM: 'INP08B Template Spend_COS'.GL Category]

For this to perform better, create a calc module for these and reference the results. Also, the hardcoding should be looked at, instead use a mapping module. Lastly, in the giant sum at the end, if space is not a problem, I would attempt to break those sums up into different line items because when a cell changes that effects any of those sums, they all get kicked off again.

ChrisMcCarthy

@rob_marshall Thank you, I shall take a look at this. Am I also correct in the fact that should minimise where the "Summary" setting is set to SUM?

rob_marshall

@ChrisMcCarthy

I can't answer that as I don't know of other line items referencing this one rely on the Sum, but in a perfect world, yes, turn off the sum if you can.

seymatas1

Hi Chris,

Creating a separate line item for ITEM(Cost Type) can help improve the model's performance.

Also, I know you didn’t ask about this, but I couldn’t help noticing the PARENT(PARENT(PARENT(ITEM(CC4 Cost Centre)))) expression. It is a good idea to move that logic into a system module and split it into multiple line items. You probably already have a system module that holds the parent hierarchy for that list anyway.

I wanted to share in case it’s helpful!

Seyma 🌷🙂