1. Reformatting dates in Tableau
In this demo, we will be revisiting the Citbike dataset where we'll demonstrate how we can make use of Tableau’s varied date time functions.
If we observe the data on the left, we can see that we have a set of date-time fields that need to be combined. Take a look at our day, month, year, hour, minutes, and seconds. This will all be reconstructed into one date time field called ridership_start_time
We’ll start with maketime.
MAKETIME is used to create a date time field for us that contains the Hours, Minutes and Seconds.
We’ll drag in the start_hours, start_minutes and start_seconds respectively.
Next, we’ll call the MAKETDATE function and drag the start_day, start-month, and start_year.
Note that the order has to be in a Year, Month, and Date format.
We’ll call this start_date.
Next, we’ll combine these two fields together.
Using the MAKEDATETIME function, we’ll combine start_date and start_time to give us one overall date field we can now utilize. We’ll call this start_date_time.
Now, with the start_date_time created, let’s evaluate how long the bike rides take.
We’ll make use of the DATEDIFF() function here to help us calculate the differences between the start_date_time and end time. I’ll call this Ridership_Duration_Minutes.
We’ll assume each ride goes on for at least a minute.
So let’s specify our date partition here as ‘minute’; note we’re using lower case. We have to be case-sensitive regarding our date partitions.
Fantastic – now we’ve got the duration of our bikes ready for analysis!
Dragging in the Ride ID and adding all to our Rows, and then dragging in our Ridership_Duration_Minutes – there we have it! We can now see all the ridership times accordingly.
Now let’s clear the canvas here and consider the final exercise – using DATETRUNC. Now when dealing with time series data, it’s quite common that we’ll seek to understand particular aggregations such as month-on-month growth, or in our case, week-on-week.
We want to prepare to calculate growth on a weekly basis. Let’s reformat our start_fields accordingly for the week using the date_trunc function.
We’ll call this calculated field start_date_weekly.
We’ll call date_trunc, we’ll pass in ‘week’ and the start_date_time field we created earlier. We’ll also include ‘Monday’ as this is what we want our week to start from.
Now that we have this, we’ll drag this into our canvas into our columns.
Note that this automatically aggregated to a Year – this is expected as the Date Trunc field returns a date data time data type. We’ll switch this to Week by clicking the pill and choosing Week Number.
We’ll then drag our ridership_id to the text marks pane and change this to a count.
We’ll modify this one last time by clicking Quick Table Calculation and selecting Running Total; this will give us our cumulative total.
We’ll then put this into a simple bar chart visualization and there we have it!
A week-on-week cumulative calculation using reformatted dates.
Now it’s your turn!
2. Let's practice!