Year over year change when the value is a string

[mlim0806]

Hello community, 

This is a tough one!

In the sample attachment I have a raw data sheet that includes years, employee and ID, position code and readiness. I would like to create a new column that calculates the change in readiness from the prior year. In other words, asked, did an employee increase, decrease, or remain the same in their readiness level to take on the position from 2022 to 2023? I imagine the logic to look something like,   "IF [year=2022] and [readiness level=Ready Soon] and [year=2023] and [readiness level=Ready Later] THEN [Change in readiness= decrease]"

Currently, I manually calculate this change using these steps,

1) Separate each year into its own tab

2) Concatenate the employee ID and the position code to create a unique ID

3) Create a new column and vlookup the previous year's readiness

4) Manually look at current year readiness and previous year readiness to determine whether the employee increased, decreased, remained the same, or if it was a new readiness level added. 

Readiness ranking:

Ready Now

Ready Soon

Ready Later

Ready Longer Term

[Qiu]

@mlim0806 
Can you clarify that for Paul, he has Readiness as "Ready soon" for year 2020.
But in Tab "2021", column "2020 Readiness", the value for Paul is "#N/A".

[mlim0806]

Great callout @Qiu .. in the raw data you'll notice the position code is different. It was to show that an employee can be on multiple positions with different readiness levels. This is why I concatenate the employee ID with position code to create a unique count for each employee/position.  

[Yoshiro_Fujimori]

Hi @mlim0806 ,

I think I followed your requirement.

I hope this helps.

Workflow

Output

For simplicity, I converted the Readiness ranking to numbers.

You can convert back to the original strings if you prefer.

Also I did not put the specific years in the column name of the sheet.

Concat	Year	Employee	Employee ID	Position Code	Readiness	Readiness_PrevYr	ChangeFromPrevYr
23569999	2021	Jeannie	23569	999	2	2	Same
52631111	2021	Drake	52631	111	3	4	Decrease
53896222	2021	Sean	53896	222	4	4	Same
57412111	2021	Paul	57412	111	3	0	New
65214111	2021	Kyle	65214	111	3	2	Increase

[Qiu · Accepted solution]

@mlim0806 
I had almost same approach as @Yoshiro_Fujimori 😂

[mlim0806]

@Qiu @Yoshiro_Fujimori  this is great, I didn't think to assign a numerical ranking for the readiness. @Yoshiro_Fujimori I was not able to open your workbook due to version :( 

If we decide to add a new readiness level "Ready in Emergency", but it doesn't necessarily have a spot in the ranking, it's a category of its own. Could I assign the rep to 0 and it would then be... ? 

Expression: if isnull([Row-1:Rep]) then 'New' else if [Rep] - [Row-1:Rep] >0 then 'Increase' else if [Rep] - [Row-1:Rep] =0 then 'Ready in Emergency' elseif [Rep] - [Row-1:Rep] < 0 then 'Decrease' else 'Remained the same' endif endif

[Qiu]

@mlim0806 
Glad to be any help.
[Rep] - [Row-1:Rep]  is having 3 status >0, =0, <0.
else 'Remained the same' is actually using the condition =0. so we can not use =0 for  'Ready in Emergency' anymore.

Can we assign other condition for  'Ready in Emergency'?

[mlim0806]

The tricky part is, this is also a manual piece. Current state, we only know if the individual is also Ready in Emergency by looking at additional comments which is a free text field. Is it possible to create some version of "Find = Ready in Emergency" in [Comments] and assign 1 or 0, or Yes or No value in a separate column? 

Updated file attached. 

[mlim0806]

 @Yoshiro_Fujimori  Hello, I am revisiting this item as it appears the solution may not have worked on all records as expected on my actual dataset. Are you able to resend your solution on a downgraded Alteryx file type yxmd?