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? 

fadalian_1-1596558583447.png

Thanks in advance!!

Any advice @einas.ibrahim?

Best Answer

  • 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

    image.png

    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.

Answers

  • 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?

  • @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

  • 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)?

  • 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!

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