Lookup in Anaplan but with different dimensions

Hi Everyone,

 

I have 2 modules:
The first being 'Module - Calculate' with 2 dimensions: 'Site and Services' and Time. It has 2 line items, number of days and total cost.

The second 'Module - Rate Table' with 2 dimensions: 'Site' and 'Numbers'. It has 2 line items, number of days and Rate.

 

Reason for separating the Rate table is because the rate would apply to all services associated with the site. 

The 'Numbers' is just a list containing numbers 1,2,3,4,.... 99, 100. 

 

The formula I can't seem to get is how to the 'Module - Calculate's total cost. I need to multiply the number of days by the rate. i have made both module's 'number of days' format type list(Numbers). However when i enter the calculation as Module - Rate Table.Rate[Lookup:Module - Calculate.Rate], the error msg says: Level mismatch on common dimension. 

 

How can i get the Calculate module to lookup the Rates module when they have a different dimension?

Answers

  • Hi,

    I'd start with thinking how the dimensions are related between modules. If you have dimensions which are not included in both modules, Anaplan will by default use the top member of the list in the source. If this is not appropriate you must tell the formula to use a specific list member in the source either with fixed selection [SELECT:] or more dynamic lookup [LOOKUP:].

     

    The source module (Module - Rate Table) includes dimensions:
    Site
    Numbers

     

    The target module (Module - Calculate) includes dimensions:

    Site and Services
    - How does this relate to the rate table? Are sites and services hierarchical? You could have a property module (e.g. Sites and Services properties with only one dimension in it) where you define a site for each list member (either with parent-function in case they are hierarchical or it could be populated with import which is better for performance). Then your formula would look a bit like 'Module - Rate Table'.Rate[LOOKUP: 'Sites and Services Properties'.Site]

     

    Time
    - You will fetch the same rate for all months/weeks/years because the source does not include time)


    After this the formula still tries to use the top member of the numbers list. What is the purpose of this list? Can you elaborate this a bit more?

  • @Jaakko @aatan 

    I would always try and avoid SELECTS if possible.  Using lookups is more flexible, even for top levels

    You don't mention that Rate is a line item in the Calculate module, but if it is a value, then you cannot use rate as a lookup parameter.  SUMs and LOOKUPs only work with formatted list items

    Assuming Sites and Sites and services are the same list (or Sites are a subset of Sites and services), and Rate is a value, you should be able to use the No of days line item as the lookup.

    I would read the following articles which explain the concept and relationships for SUMs and LOOKUPs well

     

    https://community.anaplan.com/t5/Best-Practices/Converting-data-between-line-items-with-different-lists/m-p/37946#M1740

     

    https://community.anaplan.com/t5/Best-Practices/Need-help-with-SUM-and-or-LOOKUP-functions/m-p/37145#M1686

     

    I hope that helps

    David