SUM function usage on different dimensionality

VarshniR

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

ChrisAHeathcote

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

Niranth

@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

VarshniR

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

DavidSmith

@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

VarshniR

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.

 

ArunManickam

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

VarshniR

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]

anirudh

@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

ArunManickam

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

DavidSmith

@VarshniR 

I hope this helps

 

David

VarshniR

Thanks a lot Chris.

ChrisAHeathcote

@VarshniR 

You are welcome. 

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

Chris