2022: Week 50 - Solution
Solution by Tom Prowse and you can download the workflow here.
We continue with our HR themed challenges this week and take a look at when a candidate is withdrawing from the hiring process. Hopefully this analysis can allow the Talent Acquisition team understand if there are any trends or areas that they need to address.
Step 1 - Rank Status
First we want to rank the status based on the Timestamp field. We can do this easily in Tableau Prep by creating an analytical calculation to create the rank:
Rank
This allows us to have the status that is ranked #1 (candidate withdrew), but we also want rank #2 which will be the status before the candidate withdrew - this is the key piece of information that we are looking to analyse.
The get the previous status we first need to isolate the Candidate Withdrew status rows. To do this we want to filter to keep only 1 in the Rank field, then keep only 'Candidate Withdrew' in the Status field.
We can then join this back onto our original table using candidate_id and position_id.
This gives us the total number of withdrawals grouped by the previous status.
From here we only want to keep only the 2's from the Rank field and keep only the candidate_id, position_id, and status fields. We can also rename the status field to status_before_withdrawal. Our table should look like this:
Step 2 - No. of Withdrawals
We can now calculate the number of withdrawals based on the previous status using an aggregation where we group by status_before_withdrawal and sum number of rows (we can rename this to withdrawals):
Now we want to calculate the total number of candidate/positions that there have ever been in each status. For this we need to create a new branch from the step where we calculated the rank. From this branch we can aggregate by grouping by status and sum number of rows (rename this to total_in_status):
We can then combine both of these counts by joining on status_before_withdrawal = status and we want to return all of the fields from the branch where we calculated the total number of candidate/positions.
After the join we can clean the table by removing the status_before_withdrawal field and renaming the status to status_before_withdrawal. Then finally we can replace any null values in the withdrawals field with a 0.
Our table should now look like this:
Step 3 - % of Totals
The final step this week is to calculate the % of candidates who withdrew after each status. To do this we can use this calculation:
pct_withdrawn
ROUND(
[withdrawals]*100
/
[total_in_status]
,1)
As by the requirements we have also rounded our % to 1 decimal place.
We can then remove any additional columns and our output should look like this:
You can download the full outputs here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14, @TomProwse1 & @kelly_gilbert
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
