Help needed for Lookup formula
Hi I need help with solving for a lookup formula am trying to build in Level 2 Supply Chain Model. Have been trying unsuccessfully to get it to work so need some advice on the solution.
Here's the situation:
1. Lookup formula needed for Growth Rate % at SKU Level in DEM03 Demand Forecast module (time in weeks);
2. Growth rates in weeks already exist in DEM02 Volume Growth Rates by Week module;
3. DEM02 has P1 Product Family as a dimension which would suggest I need to disaggregate this to P2 Products and then P3 SKU;
4. SYS08 SKU Details has Code, Product and Product Family as line items. Code is Text formatted while Product is list formatted (P2 Products) and Product Family is list formatted (P1 Product Family). Code references dimension P3 SKU via a CODE formula [ CODE(ITEM('P3 SKU')) ];
5. SYS09 Product Details has Code, Product Family and Size as line items. Product Family is list formatted (P1 Product Family).
Correct me if wrong but believe the mapping should be with reference to the SY08 SKU Details module which seems to have all of the elements needed for LOOKUP to work. I have tried various combinations of the line items Code, Product and Product Family in the formula editor but getting errors in each instance. For example 'DEM02 Volume Growth Rates by Week'.Growth %[lookup: 'SYS08 SKU Details'.Code, lookup: 'SYS08 SKU Details'.Product] returns me a "Level mismatch on common dimension".
I also tried 'DEM02 Volume Growth Rates by Week'.Growth %[lookup: 'SYS08 SKU Details'.Code, 'SYS08 SKU Details'.Product Family] which returns me "The formula for 'DEM03 Demand Forecast'.Growth Rate % is invalid".
What am I doing wrong?