Use formula to integrate data at multiple levels of list hierarchy

awestphal

Hello, my model has a list hierarchy of "FND01 Fund" and "FND02 Series". FND01 has a top level of "All Investments."

The model also contains three modules with data values at each level of the hierarchy - Total, Fund, and Series - and I want to integrate this data into a unified report as shown below:

I need to write a formula in the "IRR" line item which combines all three data sources into one report. I can build a SYS module which defines the level of each item in the hierarchy, and setup conditional logic to reference STG01/STG02/STG03 based on the hierarchy level. That method does not work, because it requires a LOOKUP function on the Fund level, and that formula cannot be combined with the "Formula" summary method.

I unsuccessfully attempted to accomplish this integration by using a staging module and the Ratio summary method, based on this suggestion:

https://community.anaplan.com/discussion/147519/data-import-with-different-level-hierarchy

I look forward to hearing your suggestions on this issue

TristanS

@awestphal I have done something like this before. The steps do it is a bit tedious but will try to explain top down. At a high level you will need the following:

1. Create a mapping module for each level of the hierarchy to push the higher level data (e.g. Fund) to the leaf level (e.g. Series) just to enable calculation
2. Logic to identify what level of the hierarchy is being calculated. E.g. Top, Fund or Series
3. Data at the various hierarchy levels (your STG modules above)
4. Consolidation module (your REP01 module above with added calculations)

For illustration, here's my output and input screenshots

Output below

Inputs (modules are captured side by side)

Now here's the breakdown

I have the list hierarchy below for as example.

Step 1. Define mapping modules

Step 1.1. The leaf level (A03) should have summary set to "First non-blank" as per screenshot below

Step 1.2 The parent modules should map back to the leaf list. Line Item "A03 for Consolidation" below. I did same thing for A01 having the same line item and formula

Step 2. Logic to identify what level of the hierarchy you are in. Refer to module below. Note getting the setup of this correct is critical.

line item Ratio Counter for example has NO dimensions. It is not a "-". It is blank

The ratio calculation for each line item are as follows:

Step 3. Input data modules at different levels.

Step 4. Consolidation module

Output of which already provided in earlier screenshot. Have a go and tell me how it goes.