Help needed for Lookup formula

ClarenceAndre_*44012
ClarenceAndre_*44012
in Modeling

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?

 

Kind regards,

 

Clarence

 

Tagged:

Best Answer

  • Akhtar.shahbaz
    Akhtar.shahbaz
    Answer ✓

    Hi @ClarenceAndre 

     

    You are almost very close. Just one thing I would like to add about the Lookup. Your lookup item should be list formatted

    X[lookup: Y] -> Y should be list formatted line item.

    In your formula, you are using 'SYS08 SKU Details'.Code, First it is text formatted & you don't need to write in the formula, just lookup with the product family because your source module DEM02 having Product family as a  dimension.'

     

     'DEM02 Volume Growth Rates by Week'.Growth %[lookup: 'SYS08 SKU Details'.Product family]

     

    Hope this helps!

    Thanks

    Akhtar

Answers

  • ClarenceAndre_*44012
    ClarenceAndre_*44012

    Dear Akhtar,

     

    Firstly thank you very much for taking the trouble to reply me over a weekend. Unexpected but very much appreciated.

     

    Second, yes it works and thanks very much! I had the Product Family in my Lookup formula but also Product (both list formatted) as I thought we needed Product to first roll up the SKUs into, following which Product Family but looks like that wasn't required? Because the Product Family relationship already establishes that SKUs will ultimately roll up into Product Families and therefore the intermediate Product step is not required?

     

    Can I understand for Lookup to work, any dimension referenced in a Lookup formula should be present on both the source and target sides? For example, the P1 Product Family dimension is present in both the DEM02 Volume Growth Rates by Week and SYS08 SKU Details modules.

     

    Kind regards,

     

    Clarence

     

Categories