2023: Week 35 - Solution

Solution by Tom Prowse and you can download the workflow here.

For our HR challenge this week we are looking at internal transfers and how we can identify how often it happens and where the HR team should prioritise. 

Step 1 - Transfer Flag

After inputting the data source we want to create a flag to identify whether an employee has transferred across to a different distribution centre. As per the requirements we first need to identify whether the employee transferred or left and returned.

To get the number of months between the different distribution centres, first we need to lookup the distribution centre in the next month:

dc_next_month 

{ PARTITION [employee_id]:{ ORDERBY [month_end_date] asc :LOOKUP([dc_nbr],1)}}

We can then lookup the next month for each employee: 

next_month 

{ PARTITION [employee_id]:{ ORDERBY [month_end_date] asc : LOOKUP([month_end_date],1)}}

Then we can use both of these to identify whether the employee had been away for longer than 2 months: 

transfer_flag

IF [dc_nbr]!=[dc_next_month] AND DATEDIFF('month',[month_end_date],[next_month])<=2

THEN 1

ELSE 0

END

As a result we should now have a flag to show if an employee has transferred

Step 2 - Tenure

Now we want to calculate the tenure for each employee based on the given requirements. 

First, we want to calculate the tenure of those who have transferred 

tenure_transfer

IF [transfer_flag]=1

THEN [tenure_months]

END

Then we can create some calculated fields based on the number of transfers and their average tenure. To do this we can create an aggregation step where we group by dc_nbr, then countd employee_ID (number of employees - ee_count), Sum transfer flag (how many transfers - xfer_count), and Avg tenure_transfer (average tenure of transferred employees - avg_tenure_months).

At this stage we can also rename these to match the requirements and make sure that the avg_tenure_months field is rounded to 2 decimal places.

Step 3 - Totals Row

Now we can add the Totals row at the bottom of our table. To do this we want to go back to the step before the aggregation, and create a new aggregation step on a separate branch. Within this aggregation we need to bring through the same aggregated fields, but this time we don't group by dc_nbr.

Then we can add a 'Totals' label as a new field called dc_nbr and union this to our other branch after the aggregation. Note, if you create a new step to rename the fields then this should become before that step. If you renamed in the aggregation step, then you may need to merge the appropriate fields.

After the union and the name changes the table should look like this: 

Step 4 - Transfer Percentage

Finally we want to calculate the percentage of employees who have transferred: 

xfer_pct 

ROUND(100*[xfer_count]/[ee_count],2)

Then we can ensure that the table is sorted correctly by dc_nbr and totals at the bottom by creating a rank field: 

And we can then hide this field so that it isn't shown in our output: 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @kelly_gilbert, @Datajedininja, @JennyMartinDS14 & @TomProwse1

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!