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].

andre.lie

Hi,

Thanks for your advice.  It really works.  

 

I previously also tried with 2 staging line items in the second module: cost center and country, both are List types.  Values are derived from the first module, where in the first module:

cost center = 'Employee L5'.Cost Center[LOOKUP: employee]

country = 'Employee L5'.Country[LOOKUP: employee]

 

while 'Employee L5'.Cost Center = PARENT(ITEM('Employee L5')), and 'Employee L5'.Country = PARENT(PARENT(ITEM('Employee L5')))

 

Then for Currency, I tried: 'Country L3'.Local Currency[LOOKUP: Country]

However, I got this error: The formula for .....Currency is invalid

 

 

Is it because of the same reason of having PARENT function in the Lookup, even though it is indirect?

 

Thanks

Andre

bdeaton

In your second module, do you have a line item called country and what is its formula, format, and applies to? Does it populate the way that you would expect (can you see countries being populated correctly?) The way you have the formula "'Country L3'.Local Currency[LOOKUP: Country]", it will be expecting a line item called "Country" within the same module that is list formatted as Contry L3.

 

There theoretically shouldn't be a problem with "indirectly" using a PARENT or any other formula in the country line item (the way I provided you also does uses the PARENT formula, so indirectly using it isn't a problem). So I'm just wondering if there is a mismatch somewhere on formatting/applies to which causes a problem. 

andre.lie

Thanks for the response.

 

The country in the second module is populated correctly. It is list formatted as Country L3.  The formula: PARENT(ITEM('Cost Center L4')).

 

I think I will use 'Country L3'.Local Currency[LOOKUP: 'Cost Center L4'.Country] as you suggested earlier.