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?

Kind regards,

Clarence

Accepted answers

Akhtar.shahbaz

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

All comments

Akhtar.shahbaz

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.

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

Hope this helps!

Thanks

Akhtar

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

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…