When dealing with temporal data, visualising events across a 24-hour period is an effective strategy to explore patterns in their occurrence. At a glance, you can gather start and end times, duration, and whether these events overlap.
Perhaps you aim to visualise the booking periods of a room to identify any double-bookings. Or, you might be managing multiple projects in a day and need to pinpoint the times when the demand for resources peaks. Plotting out the timespan of these events can provide you with a clear sense of how various activities unfold concurrently or sequentially. In doing so, you can better anticipate potential overlaps, streamline resource allocation, and optimize overall efficiency.
For my example, I’ll be using a dataset that records the viewing activities of five users on my Netflix account. Let’s delve into how I would visualise the timeline of each user’s activity in Tableau.
Preparing the Data
The fields that are needed for this visualisation are:
- A name field that identifies each unique event (e.g. project name, booking ID, username),
- The date of the event (or a equivalent identifier),
- The Start Time* of each event
- The End Time of each event, or Duration**.
*Tableau datetime fields always contain both a date and a time component. When only a time is provided, Tableau adds the date of 1 January 1900 to make it a valid datetime. Therefore I find it best to use datetime that already include the associated date. If that is not available, you can use Tableau’s string functions to merge your date and time fields.
**We’ll be using the End Time to calculate the duration of an event, so including the duration in your dataset can simplify the process. Having the End Time available as a tooltip could be beneficial, but if necessary, it can also be computed using the DATEADD function in a calculated field.
Creating the Viz
This viz is basically a Gantt chart with the events listed along the vertical axis, and time of day plotted along the horizontal axis.
To start, we first have to convert the time of day into a decimal number. This can be created using the following calculation:
01. Hour of Day//Getting the hour of the Start Time as an integer
DATEPART('hour', [Start Time]) +
//Converting minutes to a decimal number
DATEPART('minute', [Start Time])/60 +
//Converting seconds to a decimal number
((DATEPART('second', [Start Time])/60)/60)
If you do not have a Duration field yet, you can create one with the following:
02. Duration (seconds)DATEDIFF('second', [Start Time], [End Time])
Next up, we want to convert the duration of a event to a decimal number as well, to match our time of day axis.
03. Duration([02. Duration (seconds)])/60/60
Now to put it all together. Change the mark type to Gantt Bar, drop the newly created [Hour of Day] pill into Columns, and your date as discrete dates into Rows. Drop our created [Duration] into the Size mark.
You should now be able to see the timelines of activities plotted along the time of day they’ve spanned.
Next up, we’ll focus on improving this chart’s clarity and presentation.
Finishing Touches
Plotting multiple events on the same day
If multiple events or users are involved on the same date, you can drag the associated [Name] field to the Rows shelf and/or to the Color mark. This will distinctly identify each event, making it easier to differentiate between them in your visualisation.
Axis Adjustments
I like to append “:00” as suffix in the number formatting for the horizontal axis, as it intuitively indicates that the axis represents a time dimension.
It might be possible to hide the slightly inaccurate “24:00” axis label, depending on how far your data spills into the previous day or the next day. You can achieve this by setting your axis limits to -0.9 and 23.9 respectively, or by altering the tick mark intervals to exclude it. Alternatively, you can put down Constant Lines with the values 0 and 24 to mark the boundaries of the day, as I have done above.
Duration Tooltip
Visualisations offer a holistic view of data, but sometimes we require specific details to make the picture complete. Recall that, thus far, we’ve been dealing with duration either in seconds or in decimal values of hours—neither of which are especially intuitive when we are examining durations that span multiple hours. Here’s a formatted version of Duration that is more human-readable as a tooltip or label:
04. Duration (Formatted)STR(
FLOOR(SUM([02. Duration (seconds)])/60/60)
)
+ ' hr ' +
STR(
ROUND(SUM([02. Duration (seconds)])/60%60)
)
+ " min"
And that is that. This is one way you can visualise time-based data in Tableau. Hopefully you will find some of these techniques useful in your own visualisation endeavours.
You can download a copy of the workbook here.
