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

[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