Use formula to integrate data at multiple levels of list hierarchy
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:
I look forward to hearing your suggestions on this issue
Best Answers
-
@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:
- 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
- Logic to identify what level of the hierarchy is being calculated. E.g. Top, Fund or Series
- Data at the various hierarchy levels (your STG modules above)
- 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.
3 -
Hi Tristan, that solution worked perfectly! Do you have a moment to explain the technical details behind this workaround?
My theory is that by creating a line item dimensioned by the FND02 Series list in a module dimensioned by the FND01 Fund list, and then running a SUM function against that FND02 line item, it effectively forces Anaplan to run something like a PARENT(ITEM(FND02 Series)) function. I'm just baffled on the exact dimensional analysis that takes place here - I expected the SUM function to throw an error of dimension mismatch, because we are using an FND02 Series item to lookup into a data module dimensioned by FND01 Fund.
0
Answers
-
@awestphal The problem you had is that you had source data at FND01 and Total FND but your target module is dimensioned by FND02. Your target module needs to perform calculations at the dimension you had set for it which is FND02 … so you need to have all the calculations performed at FND02. So having the line item dimensioned by FND02 in FND01 and performing the SUM against it helps facilitate that. So even though the value is stored at FND01 … it pushes the data out to FND02. Hope that clarifies your question. There's a lot of other parts involved in the solution which I hope made sense.
1
