The Target module: is DEM04 --- Dimension: G3 Location, P3 SKU, Time (Months)
The Source module: DEM03 --------Dimension: P3 SKU, Accounts, Time (Weeks)
For LOOKUP to work,
- You have to determine the module where your Source data resides (DEM03) and then look at the dimensions of that source module (P3 SKU, Accounts).
- In order to retrieve a single data cell (single intersection), you have to provide all the Source module dimensions in your LOOKUP formula; Unless the Target includes that dimension.
In this case, P3 SKU is a common dimension so we don't have to worry about it.
That leaves up with Accounts, right?
Do we care about Demand broken down by Accounts? No, we are looking for the demand for P3 SKU in all Accounts. If you don't provide a LOOKUP with Accounts, the formula will return the data (demand) for all items in the missing dimensions i.e. Accounts (Top Level- All Accounts) because it doesn't know which Account data you are looking for; and that's what we need.
That leaves us with nothing to provide in the LOOKUP formula. which means we don't need a LOOKUP. A direct reference will provide you with the data you are looking for.
As to the reason SUM is not the right formula here, that is because we are looking for Demand by P3 SKU and the Source has the demand by P3 SKU. so there is nothing to SUM.
I really care about explaining how to use LOOKUP and how to think about obtaining the answer more than the correct answer.
Let me know if I need to explain any point further or even in a different way,
Good Luck