Level Mismatch on Common Dimension

andre.lie

Hi All,

 

I have a data entry module with 3 lists: versions, row id, and line item list.  I have 3 line items: person, cost center, country, with type of List and link to person (L3), cost center (L2), country (L1) lists. The input is to select person, then cost center and country is derived from the hieararchy.

 

The second module has 4 lists: versions, row id, cost center, and line item list. One of line items is currency.  I want to get the respective currency of the cost center by looking up from country (L1) list which has local currency property.

 

So I use function like this: 'Country L1'.Local Currency[LOOKUP: FirstModule.Country ]

 

However, I got error: Level Mistmatch on Common Dimension

 

Thanks

Andre

bdeaton

Your second module has Cost Center as a dimension, which is a child of the County list (which has the property you want to reference). I think what you are theoretically wanting to do is: ‘Country L1'.Local Currency[LOOKUP: PARENT (ITEM (‘Cost Center L2’))]. This formula would, for each cost center, identify it’s parent (I.e. Country) and then lookup the appropriate currency. The reason your original formula doesn’t work is that your first module does not appear to be dimensioned by Cost Center (rather, you have Cost Center as a line item).

Now, the formula above won’t work as typed - you’ll get an invalid formula error. The PARENT function can’t be within the LOOKUP function. So I’d create either a staging line item in this module or a property in the Cost Center list (I recommend this as you may want to use it again later and if you have a property set up, you won’t have to create a new line item each time you need to do something like this) that identifies the parent with the formula PARENT (ITEM (‘Cost Center L2’)). Then, reference that line item/property using something like ‘Country L1'.Local Currency[LOOKUP: ‘Cost Center L2’.Country].