Error Pulling Specific Product Data Across Modules

Isha · January 11

Please advice on this!

I have Module 1 with the dimensions: Product_XYZ (Numbered list), Location
The line item in this module is 'Order Quantity' and the Product_XYZ subset list contains Products A, B, and C.

I also have Module 2 with these dimensions: Product_LMN (Numbered list), Location
The line item in this module is also 'Order Quantity', and the Product_LMN list contains Products D, E, F, and G .

Requirement

I need to calculate a ratio where:

Ratio for Product D = Order Quantity for Product D / Order Quantity for Product A
Similarly, Ratio for Product E = Order Quantity for Product E / Order Quantity for Product A

Attempted Solution

To achieve this, I created Module 3 with dimensions: Product_LMN , Location

In Module 3, I added the following line items:

'Order Qty for Product_LMN': Pulled values from Module 2.
Dimensioned this line item with 'Product-XYZ and Location'
Order QTY for Product A'
IF ITEM(Product_XYZ) = 'Product.A' THEN 'Order Quantity' ELSE 0However, Anaplan threw an error:
"Product A is not a recognized line item or list member."

Request

What is the correct way to pull the order quantity for Product A from Module 1 into Module 3 and use it for the ratio calculation?

derektslim · January 13

Hi Isha,

You may consider creating a SYSTEM module, to map your LIST: Product_XYZ to LIST: Product_LMN. Using Product_LMN as the dimension, create a Line Item called Mapping, with Format set as LIST: Product_XYZ. Then pick the products from Product_XYZ that you want mapped to the Product_XYZ products.

Then in your Module 3, Order QTY for Product A, you leave the Dimension as Product_LMN , Location. The Line Item should point over to your Module 2 Order Quantity, and use a SUM pointing to the mapping line item created earlier (try LOOKUP if SUM does not work).

Hope this helps!
Derek Lim

Prajjwal88 · January 13

Hi @Isha ,
If you always need to consider the value of Product A for ratio calculation, one of the easiest ways to achieve this is by creating a list-formatted (Product List) line item in a system lookup module. Select Product A in this line item (the product you need to consider for ratio calculation).

In Module 3, instead of dimensioning the relevant line item with Product-XYZ and Location, dimension it only by Location. Then use the following formula:
'Module 1'.Order Qty[LOOKUP: 'SYS01 Lookup'.'Product A'].

You can then use this value in further calculations as required. Refer to the example screenshot for further assistance,

I hope this helps.

Isha · January 13

Hi @derektslim,

Thank you for the response! I tried it, but the Lookup and Sum functions didn't work in this case.

Isha · January 13

Hi @Prajjwal88,
Awesome! Thank you for the detailed explanation—it worked perfectly. I’d now like to calculate the Average of that Ratio line item. However, setting the summary method to 'Average' doesn’t seem to work. Do we need to create a separate line item for this? Please advice.

Prajjwal88 · January 14

Hi @Isha ,

Setting the summary method to 'Average' will work if the Product/Location list has a Top Level assigned, depending on the dimensions of the target module. You can check and update the Top Levels if needed.

Alternatively, if you prefer not to add a Top Level, you can refer to below article for guidance on calculating Sum/average without using a Top level.

How to Resolve “Automatic Sum of Hierarchy Not Possible” Error Without a Top-Level Item?

[Deleted User]

Oct 15, 2024

Hi Anaplan Community,
I’m encountering an error in my model: “Automatic sum over Over/Under hierarchy is not possible because the list does not have a built-in top level.”
https://us.v-cdn.net/6037036/uploads/BTUC6JVCA16K/image.png
I understand that one solution is to set up a top-level item in the list. However, I prefer not to use a top-level item. I’ve read that using a LOOKUP on a constant or mapping module can also resolve this issue, but I’m not sure how to implement this.
Could someone provide detailed steps or examples on how to use a LOOKUP on a constant or mapping module to bypass the need for a top-level item? Any guidance or best practices would be greatly appreciated!
Thanks in advance for your help!

I hope this helps. 😀

Isha · January 14

Hi @Prajjwal88,

Great! Its working. Thank you for sharing the article.

Error Pulling Specific Product Data Across Modules

Requirement

Attempted Solution

Request

Welcome!

Best Answers

Answers

How to Resolve “Automatic Sum of Hierarchy Not Possible” Error Without a Top-Level Item?

Welcome!

Welcome!

Quick Links

Categories