Order of Lookups and SUM in formulas

Hi,

I was going through a best practice video which mentioned that the order of lists in the module can help improve the performance if the lists are ordered similar to their order in General Lists section (because indexing becomes easier for the engine).

I was wondering if there is a similar best practice when it comes to the formulas itself?

Is there an order in which we should be putting the SUM/LOOKUP functions (assuming the formula has 2 lookups together)?
If you are multiplying 2 different numbers say an assumption % (from a small assumptions module) and a total number (from a module of same size), does the order of the selection matter in terms of performance? E.g. Is "Assumption%[Lookup:X] * Total" quicker than "Total * Assumption%[Lookup:X]" ?

Looking forward to the response from the community.

Accepted answers

DavidSmith

@shreyak.garg

Great question.

I believe the answer is no. The formula is broken down into the various parts and the SUMs are always completed first., then the LOOKUPs, then the remainder. Also if you have multiple SUMs or multiple LOOKUPs, the order doesn't matter. However, for readability, and consistency, I advocate keeping the order the same. So if your dimension order is Customer, Product, Time and you have a double lookup, I would have something like:

...[LOOUKP:Mapping.Customer, LOOKUP:Mapping.Product] each time you need to use it

It is important though to ensure that the same SUM or LOOKUP is not repeated within the line item formula. If it is, then it should be split in it's own line item; that way the formula is only performed once, not many times.

Also, I'm sure you know that you should avoid SUM and LOOKUP in the same [], as per Planual 2.02-08

I hope that helps

David

All comments

DavidSmith

@shreyak.garg

Great question.

...[LOOUKP:Mapping.Customer, LOOKUP:Mapping.Product] each time you need to use it

Also, I'm sure you know that you should avoid SUM and LOOKUP in the same [], as per Planual 2.02-08

I hope that helps

David

shreyak.garg

Thanks David.

And if besides the Lookups and SUMs, there is SELECT in some form as well (assuming best practice of not have SUM/LOOKUP together), would SELECT get some sort of preference?

DavidSmith

SELECT and LOOKUP work in the same way, especially if the LOOKUP is based on a single value rather than multi dimensionally.

So in effect, you are "looking" up the value rather than "selecting" it, but it is a very similar construct

Make sense?

David

shreyak.garg

Yes, makes sense. Thanks.

I have always thought of LOOKUP to be more Module/Line Item centric and SELECT to be List/List item related.

It is good to know that they work on the same principle.

Quick Links

Can you help?

Unanswered questions

Anaplan for Microsoft 365
Using the excel addin for Microsoft 365 when I try to add a card to import data I get the message 'Cannot insert data from a custom view'. I am not using a custom view. Any ideas what I am missing here?
Data Split between Many to Many Relationship Mappings
Hello Anaplan Community, I am encountering an issue with one of my development work and would be greatly appreciated your assistance. There are 2 different modules involved in my model: 1) INP01 Percentage Input by Product (Applies To: P3 List) 2) REP01 Percentage Split by Department (Applies To: Departments List) The…
Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?