2021: Week 24 - Solution

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

The challenge this week was to look at managing unscheduled time off at Chin & Beard Suds Co. In the challenge we are looking to analyse how many people have been off and if we have had someone off every day. 

Step 1 - Build Out Dates

The first part of the challenge is to build out our data set so that we cover every date listed between 1st April and 31st March. This is where the scaffold will come in useful as we can use this to fill in any dates that we might be missing in our Reasons data set. 

To make sure we are starting on the correct date, we first need to find the first date across our Reasons table. We can do this by using an aggregate tool, and finding the Min Date: 

We now want to join this to our scaffold. To do this we need to do a cross join so that each of the rows from table is joined with all of the rows from another. Therefore, we need to create a calculated field to do this, I've just a 1 but this could be anything.

The join looks like this: 

Then as a result, our Min Date now has a row from the scaffold

Finally, to make sure that we fill in the rest of the dates, then we need to use a DateAdd function: 

Date 

DATEADD('day',[Scaffold],[Start Date])

Which fills in the dates between the 1st April & 31st May.

Step 2 - Build Out Days Off

We now need to do a similar process but with the days when people have been off. This time instead of finding the minimum date, we can just use a cross join to our scaffold straight from the reasons table. 

Instead of doing a straight cross join (1=1), this time we will use a > join clause to only return the scaffold depending on how many days off someone has had. This will allow us to find the days between the start and end date of their absence. 

The join condition looks like this:

We can then use a similar calculation as before to fill in the missing dates: 

Date Off
DATEADD('day',[Scaffold],[Start Date])

Our days off data will now look like this: 

Step 3 - Merge Tables

Now we have built out the dates on each data set, we want to combine these into a single table. To do this we are going to use a Right join where we are returning all of the dates from the first scaffold. The join condition looks like this:

As a result our merged table should look like this: 

Step 4 - Number Off Each Day

The final part of the task is to calculate how many people have been off each day. We can use an aggregation tool to find this, where we group by Date, then Count Names: 

This will allow us to answer our questions from the challenge: 

1. What date had the most people off? - 8th April, 4 people off

2. How many days does no-one have time off on? 35 days 

Here's our full output for comparison. 

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

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!