Hierarchy Mapping from flat list

gchang

Currently we report our P&L using list Entity L2 which contains all our entities as a flat list.

We want to create a new module that reports the same P&L information but not on a flat hierarchy. The dimension of this module is New Hierarchy L6 which rolls up into our highest consolidation level (TopCo)

This is the mapping module with the dimensions New Hierarchy L6 (all levels of consolidation) mapped against the line items Match L2, L3, L4, L5 which has uses the format list Entity L2

The formula in the new P&L using the new hierarchy is:

Old P&L[LOOKUP: 'Mapping Module'.'Match L6']

This formula returns the correct values for the three entities in the L6 level. However, I am stuck now on how to modify the formula to pick up all the other entities in other levels of consolidation.

All entity data is available in the old P&L module given that it is a flat list.

Any guidance would be greatly appreciated.

umangsinghal

IF

ISNOTBLANK('Mapping Module'.'Match L6') then Old P&L[LOOKUP: 'Mapping Module'.'Match L6']

ELSE IF

ISNOTBLANK('Mapping Module'.'Match L5') then Old P&L[LOOKUP: 'Mapping Module'.'Match L5']

ELSE IF

……. so on.

Use this IF ELSE statement to get values on all hierarchies.