4 Replies Latest reply on Jun 8, 2018 8:03 AM by Harshil Sutaria

Lookup and Fixed

Hello :

I've details of 10000 employees repeated for every month, with Month being a column value. So the first 10K correspond to January and the Second 10K correspond to Feb and so on. There can be additions/deletions of employee records based on the number of hires and terminations. I've a column for the 'Employee level' ranging from '1' to '7'. These numbers convey the designation of employees. I'm trying to create a calculated column to see if there is a change in employee designation month over month.

I'm usimg the following expression:

{ fixed [emplid] : lookup(ATTR(employeelevel,-1)}. This is incorrect as I'm using ATTR in FIXED LOD. is there a way to modify this expression ?

Thanks

Nishanth

• 1. Re: Lookup and Fixed

I'm not sure there's need for the LOD calc here. You want to compare the current value with a previous. Therefore would something like:

IF attr([employeelevel]) = lookup(attr([employeelevel]),-1) THEN 'no change' ELSE 'change' END

This formula may give an agg/non agg error, in which case you may need to wrap the output in attr as well. Note the Compute Using for LOOKUP will also need to be set to make sure the correct prior value is returned.

• 2. Re: Lookup and Fixed

Hi Andrew :

Thank you for the reply. By previous value I mean the previous month’s value. If I do not fix the emplid, will I not get the job level of the employee right above the current one instead of the same employee’s previous month’s details?

Thanks,

Nishanth Reddy Konkala,

Analyst, GDIM

Strategy and Analytics

JLL

tel +1 312-228-2682

www.jll.com<http://www.jll.com/>

• 3. Re: Lookup and Fixed

You need to use the Edit Table Calculation - Compute Using to make sure the LOOKUP returns the value from the previous month and not the previous employee in the list. This can be tricky, I suggest you use trial and error on a small sample to understand what is happening.

• 4. Re: Lookup and Fixed

Did you find any solution besides handling this in the data? I have the same issue with a different data set.