2021: Week 18 - Solution
Solution by Tom Prowse and you can download the full workflow here.
For the month of May we are going to be building on the challenges that we released back in January. In January we focussed on some fundamentals that would help people get started with Preppin' Data without jumping into some of the more challenging tasks.
This week we are focussing on Dates and how we can prepare the data to create a Gantt chart for our dashboard in Tableau Desktop. Here's how we solved it!
Step 1 - Calculate Completed Date
After inputting the data, we want to create a clean step where we can create a calculated field to calculate the completion date for each of our tasks.
Completed Date
DATEADD('day',[Completed In Days from Scheduled Date],[Scheduled Date])
We can then can the data type to a Date, and rename the 'Completed In Days from Schedule' to 'Days Difference to Schedule'.
Our table now looks like this:
Step 2 - Pivot Task
The next task is to transform the shape of our data by pivoting each task into it's own separate column. Before pivoting, we need to remove a couple of fields to ensure that we have only one row for each combination.
By creating a new clean step, this is the start of our first branch. Here we want to remove the Schedule Date and Days Difference to Schedule fields, this will ensure that we don't have nulls after the pivot.
Now we are ready to create the rows to columns pivot, where we bring in Task and Min Completed Date:
As a result of the pivot, our table should now look like this - with a separate column for each task:
Step 3 - Calculate Time Difference
The next step is to calculate the difference between Scope to Build time and also the difference between Build to Delivery time. For these calculations we are going to be using the DateDiff function to bring back the number of days between each date. The calculations look like this:
Scope to Build Time
DATEDIFF('day',[Scope],[Build])
Build to Delivery Time
DATEDIFF('day',[Build],[Deliver])
We should now have two new fields with the number of days between the dates:
Step 4 - Pivot Tasks
We're now ready to pivot our task fields back the other way so that they are all in a single column. To do this we want to use a Columns to Rows pivot with the Build, Deliver & Scope fields in the Pivoted Fields:
Then after renaming the appropriate fields our table should look like this:
Step 5 - Join Original Data
It's time to look at bringing back all of the original data that we removed earlier (Scheduled Date & Days Difference to Schedule) so we need to create a new branch after the step where we calculated the completion date.
We don't need to do anything to this step, but this can then be used to join to our other branch. The join conditions are:
Then after the join we can remove some of the duplicated fields so that our table looks like this:
Step 6 - Calculate Weekday
The last step this week is to calculate the weekday that each of the tasks was completed on. To do this we can use the DateName function with the following calculation:
Completed Weekday
DATENAME('day',[Completed Date])
Then we should have our final output that looks like this:
Here's our full output for comparison. Remember we don't care about the order of the columns or rows as Tableau Desktop will import them in whatever order they come in!
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!
