DataVizHow-ToTableauTemplatesTimelines
by Ken FlerlageJuly 28, 2018Read
A few weeks ago, David Pires reached out to me with a challenge. If you read my blog regularly, you know I love a challenge!! Anyway, David was creating a visualization on the 24 Hours of Le Mans endurance car race. He wanted to include a timeline showing each year of the race—back to 1923. But, he wanted a curved timeline that started at the top and followed a sort of snake pattern to the bottom.He sent me the following hand-drawn idea:
In addition to this timeline looking a bit like a race track, the curved effect would also save space as 90+ years is a lot to show on a horizontal or vertical timeline.
I accepted the challenge and am pleased to say that we were able to get the timeline working. Here’s David’s brilliant viz, which happened to win Viz of the Day on June 19.
The first version locked you down to showing one decade on each row. But some time after David published his viz, it occurred to me that this curved timeline might be of value to others, especially if it were more configurable, so I revisited it and made it so that you can show any number years on a row. For instance, here’s a version showing one decade on each row:
And here are versions with 20 years and 3 years on a row, respectively.
Why?
Before we jump into how this works and how you can use it, perhaps I should first address the question of why you might want to use this. Well, I’ll be the first to admit that this is probably a niche use case. But, I think that space could be a big driver for using something like this. When building a timeline in Tableau, you’d typically have two options—horizontal or vertical—but when you have 90+ years to display, like David, both of those options could prove problematic from a space usage standpoint. Your viz is either going to be very tall or very wide and the timeline will have to stretch the entire height or length of the viz. Using a curved timeline, especially with a configurable number of years per row, might allow you to use your space more efficiently.
And to help demonstrate this point, here are a two visualizations (David Pires shared both with me), from the Guardian and BBC, respectively.
How?
So, if you’d like to use a curved timeline like this in a visualization, keep reading and I’ll show you how. I’m going to break up the how-to into two sections. The first will show you how to take my template and apply your own data. The second will take a deep dive into the calculations used to create it.
Let’s start with using your own data in my template. The template consists of an Excel data source and a Tableau workbook. The Excel template (you can find it here) includes two worksheets:
Years – Your year data. The template includes only a single column, Year, but you’d of course, want to include other columns as well.
Densification – List of 20 points to be used for the data densification required to draw the curves. You don’t need to do anything with this, other than making sure it’s in your data set. But, if you wish to see how it’s used, then be sure to read the calculations section.
In Tableau, these two sheets are joined together using a join calculation of 1 = 1, so that each year will have a set of 20 points.
Your first step will be to update the Years worksheet with your own data. Once this is populated, then you need to connect it to Tableau. Start by downloading the Tableau workbook from my Tableau Public page. Then edit the data source and connect it to your Excel data. The workbook should update automatically to reflect your data. Next you’ll need to update the Years Per Row parameter to your desired setting. Depending on how many years are in your data set and how many years you choose to show on each row, the timeline could look squished or stretched. To correct this, just resize the sheet (on the dashboard) or resize the dashboard itself until you have smooth curves. Finally, you can do whatever you like with the chart—thicken the lines, resize the circles, change the colors, add filters, update tooltips, etc. just as you normally would.
Here’s a fully interactive version with years 1900-2018.
Update February 4, 2020: As you may see in the comments, people regularly ask me how to make this template work for dates other than years (for example, specific dates or decades). My suggestion is that you leave the template as is--don't try to modify it to work with a different type of date. Rather, populate the Year field with sequential values then add another field with your actual date. The template will draw the chart using the Year field, but you can then place your date in the tooltips, on the labels, etc. so that it displays your date rather than the fake year.
Calculations
If you’re reading this section, that means you’re interested in the calculations used to create this viz. Before I jump into the specifics, here’s a high-level summary of what the calculations would need to do:
- The first row would go from left to right, then curve around to row 2, which would go right to left. Thus, odd rows go left to right and even rows go right to left.
- Drawing the straight lines is fairly simple, but the curves add complexity. Trigonometry would be required for this (for a refresher, see Beyond Show Me: Trigonometry).
- Straight lines can be connected easily from year to year, but curves will require some data densification, thus the reason for including the point data noted earlier.
I think it’s important to note here that these calculated fields were the result of iteration. I started with some simple assumptions—for example, I started by making each row a single decade. And I started by only drawing straight lines. Here’s an early iteration I shared with David:
Once those basics were worked out, I then added in some of the more complex elements, slowly iterating until I achieved the final result. Calculations are never born fully formed—they are typically the result of lots of iteration, experimentation, and testing.
Another thing I’d like to point out is the use of comments in my calculated fields. I’ve written about this before (see Comment Your Calcs). Without comments, I would personally have no idea about the logic behind many of these calculations, nor would others, so for me, these comments are critical to both my future understanding and for the understanding of others.
The calculations rely on two parameters:
Y Spacing – This indicates the spacing between each row. By default, it is set to 1. There is little reason to change this as automatic sizing of a worksheet on a dashboard will take care of any adjustment you want to make in the height.
Years Per Row – This is the number of rows you wish to plot on each row. It is set to 10 by default.
With all that out of the way, here are the calculated fields used to create the timeline. As I’ve commented each of them, I won’t go into further detail about how they work or what they’re doing. If you have any questions about them, feel free to leave a comment on this blog or reach out to me directly.
First Year
// Get the first year of the first decade.
// This will truncate numbers to a decade (i.e. 1925 to 192)...
// ...then change that to the first year (i.e. 1920).
INT({FIXED : MIN([Year])}/[Years Per Row])*[Years Per Row]
Last Year
// Get the last year of the data set.
{FIXED : MAX([Year])}
Number of Points
// Number of points used for densification.
// Strictly speaking, the number of points is this + 1 since Point starts at 0.
{FIXED : MAX([Point])}
Offset
// The number we'll need to subtract from the calculated Y to get the final Y.
// We'll calculate the Y coordinate based on the decade (i.e. 1925 will be decade 192).
// We want to bring it back to start at Y=0 so the following will give us the offset (i.e. 191).
INT([First Year]/[Years Per Row])
ID
// Unique, ordered ID of each point. Will be used to define the path of the line.
[Year]*100 + [Point]
Angle Spacing
// Angle spacing for the curves, each of which are a semi-circle and, thus, 180 degrees.
// We'll break up the 180 degrees by the number of points.
180/([Number of Points]+1)
Angle
// Angles on the right will run from -90 (top) to 90 (bottom).
// This is reversed from normal because the Y axis is reversed.
IF [Curved]<>"NONE" THEN
-90+([Point]*[Angle Spacing])
END
Row
// This will get the decade of the given year (i.e. 1925 would be 192)...
// ...then it will subtract the offset to bring it back so the first row starts at 1.
(INT([Year]/[Years Per Row])-[Offset])+1
Y
// Calculated Y coordinate. This will get the decade of the given year (i.e. 1925 would be 192)...
// ...then it will subtract the offset to bring it back to 1.
(INT([Year]/[Years Per Row])-[Offset])*[Y Spacing]
Center Y
// Center point for drawing the curve using trigonometry...
// ...Basically just halfway from the row to the next.
[Y] + [Y Spacing]/2
Event
// Is this an actual year event (as opposed to a point used for densification)?
// This will just grab the first point in our data densification data (0).
IF [Point]=0 THEN
[X]
END
X
// Calculate the X coordinate on which the point will be plotted.
// Odd rows will go left to right. Even rows will go right to left.
IF [Row]%2 = 1 THEN
IF [Year]=[Last Year] AND [Point]<>0 THEN
// If this is the last year, we don't want to keep drawing after plotting it once, so stop here.
NULL
ELSE
([Year]%[First Year])%[Years Per Row] + [Point]/[Number of Points]
END
ELSE
IF [Year]=[Last Year] AND [Point]<>0 THEN
// If this is the last year, we don't want to keep drawing after plotting it once.
NULL
ELSE
[Years Per Row]-1-([Year]%[First Year])%[Years Per Row] - [Point]/[Number of Points]
END
END
Curved
// Should this point be part of the curved end?
IF [X]>([Years Per Row]-1) THEN
"RIGHT"
ELSEIF [X]<0 THEN
"LEFT"
ELSE
"NONE"
END
X with Curve
// X coordinate adjusted to account for the calculated curve (using trig).
// The right curve will go right, past the last plotted year(positive).
// The left curve will go left, past zero (negative).
CASE [Curved]
WHEN "RIGHT" THEN
([Years Per Row]-1)+0.5*COS(RADIANS([Angle]))
WHEN "LEFT" THEN
0-0.5*COS(RADIANS([Angle]))
ELSE
// This is part of the straight line, so use calculated X.
[X]
END
Y with Curve
// Y coordinate adjusted to account for the calculated curve (using trig).
// The Y coordinate is not impacted by whether the curve is on the left or right.
IF [Curved]<>"NONE" THEN
[Center Y]+([Y Spacing]/2)*SIN(RADIANS([Angle]))
ELSE
// This is part of the straight line, so use calculated Y.
[Y]
END
Thanks for reading. As always, if you choose to use a curved timeline in one of your visualizations, please be sure to share it with me as I’d love to see it!!
Update May 4, 2020: Soha Elghany recently build a publicly-available Alteryx macro that allows you to easily output the data to create a curvy timeline. I'm a big proponent of preparing your data ahead of time, when you can, so if you'd like to use this, you can check it out here: Curvy Timeline Macro
Ken Flerlage, July 28, 2018
Twitter | LinkedIn | Github | Tableau Public
