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

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…