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?

Accepted answers

All comments

Jaakko

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?

DavidSmith

@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

Quick Links

Can you help?

Unanswered questions

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?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…