Order of Lookups and SUM in formulas

shreyak.garg
shreyak.garg
in Modeling

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?

 

  1. Is there an order in which we should be putting the SUM/LOOKUP functions (assuming the formula has 2 lookups together)?
  2. 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.

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓

    @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

Answers

  • shreyak.garg
    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
    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
    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.

Categories