Lookup or SUM between Modules with Different Dimensions

Hi all and thanks in advance.

I just want to lookup an amount from one module to another one. However, they have different dimensions, so, I created a unique code to work as link between those Modules (it is a string type).

How can I perform the Lookup or Sum using this unique code? I mean, I try lots of ways, and it is still not working. It should not be that hard. Right?

Thanks in advance!!

Any advice @einas.ibrahim?

Accepted answers

einas.ibrahim

Hey @fadalian

Yes, there should be a way. Having a direct relationship between the modules is not required. but there should be at least an indirect relationship or a mapping. It should be clearer below.

So,

We are writing the formula to retrieve the amount in Module B ( the target) for a specific item in the list S. [For example the amount for list Item "Ball"]
Items in that module are identified by the dimension S.
We need to reference Module A (the source).
Items in module A are identified by X and Z. [For example the amount for something Red and Small or Blue and medium]

In order for module A to return a unique value, we have to give it the X and Z associated with the item we want.

For example, if X is a Color and Z is Size, in order to identify the unique amount I have to know which color and size combination I'm looking for.

The formula to access the amount would be something like this
Source A.Amount[LOOKUP: Mapping.'Color (X)', LOOKUP: Mapping.'Size (Z)']

If we don't provide these 2 pieces of information to module A when referencing the amount, the module won't know which value are we looking for.

We need to get the X and Z related to the S.

For example for the item Ball in the S list, what are the color and size?
We have to find a mapping that maps items in S to those of X and Z. This could be in separate mapping modules or as a list formatted line item in the target module.

Below is the example using a mapping module

The mapping Module tells me the X and Z values corresponding to the S value in the target module.

For Example for item Ball, Color is Red and Size is Medium

So first, make sure you have this mapping available

Then construct your LOOKUP to pass the X and Z values related to S. This way you will be able to get the amount from the source module (with X,Z) to the target module (S)

Source A.Amount[LOOKUP: Mapping.'Color (X)', LOOKUP: Mapping.'Size (Z)']

This answer might be a bit dry, so if you have any questions please let me know.

All comments

einas.ibrahim

Hello @fadalian

You are right, it shouldn't be that hard.

Let's work through this methodically together.

First, you don't need a unique code (unless there is a reason I'm not aware of)

Do you have any relationships between the dimensions of Module A (the source) and Module B (the target)?

for example Parent-child?

rob_marshall

@fadalian

As @einas.ibrahim stated, it shouldn't be that hard, but this what sets "good" architecture apart from "bad" architecture. To follow up on what @einas.ibrahim asked, are there any "bread crumbs" or artifacts found in modules with lists Z or X that connect or link to list S?

Rob

fadalian

Thanks for your response, as always @einas.ibrahim!!

Well, I think the reason I made the approach with a unique code is the fact that the architecture consolidated within the Model I am working on is not that polish, it is kinda rough in the edges. I mean, in some cases, lists in here dont have parents or hierarchies, even though they could be related.

So, getting back to this scenario, and taking into account the background I just mentioned, there are not relationships established between the dimensions in the two Modules. In this case, is there a way to perform some sort of Lookup or Sum in order to obtain an amount having a unique identifier in both Modules with different dimensions (with no relations established)?

einas.ibrahim

So,

We are writing the formula to retrieve the amount in Module B ( the target) for a specific item in the list S. [For example the amount for list Item "Ball"]
Items in that module are identified by the dimension S.
We need to reference Module A (the source).
Items in module A are identified by X and Z. [For example the amount for something Red and Small or Blue and medium]

In order for module A to return a unique value, we have to give it the X and Z associated with the item we want.

For example, if X is a Color and Z is Size, in order to identify the unique amount I have to know which color and size combination I'm looking for.

The formula to access the amount would be something like this
Source A.Amount[LOOKUP: Mapping.'Color (X)', LOOKUP: Mapping.'Size (Z)']

If we don't provide these 2 pieces of information to module A when referencing the amount, the module won't know which value are we looking for.

We need to get the X and Z related to the S.

Below is the example using a mapping module

The mapping Module tells me the X and Z values corresponding to the S value in the target module.

For Example for item Ball, Color is Red and Size is Medium

So first, make sure you have this mapping available

Then construct your LOOKUP to pass the X and Z values related to S. This way you will be able to get the amount from the source module (with X,Z) to the target module (S)

Source A.Amount[LOOKUP: Mapping.'Color (X)', LOOKUP: Mapping.'Size (Z)']

This answer might be a bit dry, so if you have any questions please let me know.

fadalian

Thank you once again for your help @einas.ibrahim !! I am telling you again, you are a geniuos! It worked just fine !!

You have helped me understand how should I approach this situation.. I was still trying to think it like it was Excel.. but it is not. I should have been thinking in an Anaplan´s way... knowing the importance of Dimensions.

Thanks again and best regards!

fadalian

Thanks for your help Rob!!
I will make sure to keep my eyes focus on the importance of Good architecture within Anaplan.
I was able to solve my issue readjusting my approach.
Thanks again and best regards.

einas.ibrahim

Hey @fadalian

I am glad I was able to help solve the issue and even more glad that now you understand the concept. That's always my goal.

Quick Links

Can you help?

Unanswered questions

Can Please explain the difference between a general list,a module,and action in Anaplan.
Can please explain why we create a general list ,a module,and Action ,and what purpose each one serves.🙂
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
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.…