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.
Chris1
Comments

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
0 

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.
0 
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
0 
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]0 
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 multidimensional format
I hope this clarifies
Regards,
Anirudh
0 
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
Arun0 
0

Thanks a lot Chris.
0 
0