SUM function usage on different dimensionality

Hi,

Please let me know if anyone can help me on the below question.

 

I was trying to analyze an existing module. Here is an simple example of the logic I was trying to analyze.

I have 2 modules namely Module 1 and Module 2. Module 1 has a line item 'Total' and Module 2 has two line items BU and Hours. 

The formula for 'Total' is Module2.Hours [SUM:Module2.BU].

Note- The BU is list formatted which contains its list items in text format and Hours is a number formatted.

 

Please can anyone help me on the logic here.

However, i tried reproducing this formula in similar other modules, it threw me an error that it is of different dimensionality.

 

Please can anyone let me know the logic the above formula holds in case of different dimensionality.

 

Regards,

Varshni

Best Answer

  • The SUM and LOOKUP formulas are used to map dimensions between modules where a direct link does not exist.

     

    Therefore, in order to successfully reproduce the formula in other modules it must contain enough mapping definitions in order to establish the required links.


    Example - if you are using SUM to aggregate hours by BUs and employees in a target module where the source contains just the BU dimension you must be map in the target module formula how each employee relates to each BU. Say you have an employee dimension in the target in addition to BUs which is not present in the source you must specify how employees are linked to each BU. This can be achieved by using a mapping module with the employee dimension and a LIST FORMATTED LINE ITEM of the BUs. Select a BU for each Employee and use SUM to build the link between the target and source modules. 

     

    The SUM would look something like this;

    = Module one.Hours[SUM: Mapping.Employees]

    Module one is the source contain details of the hours by BU
    Mapping is the mapping module telling the target which BUs in the source relate to which employees in the target.

    SUM is used when you have many aggregating into one - as above

    LOOKUP is used when aggregating one into many - which employee belongs to which BU

    I hope this helps to clear the mist that may have formed from all the other comments.

    Chris

Answers

  • @VarshniR 

     

    I think you should be using LOOKUP in this case and not SUM.

    This formula should work in module 2:

    Module 1.Total[LOOKUP: BU]

    This requires that BU list is a dimension in module 1 and a list formatted line item in module 2

     

    Regards,

    Niranth

  • Thanks Niranth.
    Yes, even I'm of the same idea that we need to use LOOKUP but unfortunately this model was not built by me. I'm just analyzing the existing or implemented system.
    It would be great if you could help know the existing logic works.
    Thanks,
    Varshni
  • @VarshniR 

    As @Niranth alluded to, SUM and LOOKUP will only work with formatted list items not text

    If you have the BU as Text, you will need a FINDITEM(BU, BU Text) formula to transform the Text to the list and then you can use either SUM or LOOKUP

    David

  • Hi,

     

    PFB the screenshots of the issue. I have blurred up the data for security reasons. Request you to please through the formula and let me know the logic.The BU line item is list formatted.

     

    Capture.PNG

  • Your Module 1 should have applies to as 'BU'. If it does not have BU, then it will throw the error that you are seeing.

     

    Thanks

    Arun

  • Thank you Arun.
    Its not about the error. My question is I just want to know what the formula in module 1 holds or I just want to understand the logic or the meaning or analysis of the below formula
    'Capacity - Analysis (Input/Output) - Actual'.'Total Expected Working Hrs.'[SUM: 'Capacity - Analysis (Input/Output) - Actual'.BU]
  • @VarshniR 

    In the destination module, when you're saying SUM is applied it means that the list to which it is applied, in this case your BU list, is a dimension in the destination module and a property/line item in the source module.

     

    Refer this part of your formula to know which list is being used for SUM

    [SUM: 'Capacity - Analysis (Input/Output) - Actual'.BU]

    And check which list is being used as the format for the line item referred in brackets

     

    Essentially, SUM converts data from a flat format into a multi-dimensional format

     

    I hope this clarifies

     

    Regards,

    Anirudh

  • To add to what @anirudh has said,

    In Your source module, the list item can repeat any number of time, in your destination module, it is a dimension so it can appear once for every other dimension combination.

    Kindly take a look at anapaedia for how sum, lookup, select are used.

    Thanks
    Arun
  • @VarshniR 

    I hope this helps

    2019-07-24_12-04-38.png

     

    David

  • Thanks a lot Chris.
  • @VarshniR 

    You are welcome. 

    Please reach out via these forums if you have further queries. 

    Chris