Use formula to integrate data at multiple levels of list hierarchy

awestphal · 2023-08-30T22:30:55+00:00

There was an error rendering this rich post.

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

Accepted answers

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:

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.

awestphal

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.

All comments

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:

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.

awestphal

Hi Tristan, that solution worked perfectly! Do you have a moment to explain the technical details behind this workaround?

TristanS

@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.

AnyaS

@TristanS nifty setup!

Quick Links

Can you help?

Unanswered questions

AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
Using time for something not time related - special characters :)$
We had a recent challenge with the need to "clean" invoice data descriptions from special characters. This was done previously in SQL with the a long convoluted formula "REPLACE(REPLACE(REPLACE('Ana&plan', '&', '') that would have to edited by a developer in case any new special characters came through. Using the replace…