Error Pulling Specific Product Data Across Modules

Isha

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:

1. 'Order Qty for Product_LMN': Pulled values from Module 2.
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

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

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

Hi @derektslim,

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

Isha

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

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.

https://community.anaplan.com/discussion/159305/how-to-resolve-automatic-sum-of-hierarchy-not-possible-error-without-a-top-level-item

I hope this helps. 😀

Isha

Hi @Prajjwal88,

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