# Curvy Timelines in Tableau

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

He sent me the following hand-drawn idea:

*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.

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.

**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:

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:

- 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!!

Ken Flerlage, July
28, 2018

Hi Ken!

ReplyDeleteThis is an amazing resource, thank you!!

I do have a question regarding the years per row.

Could you advise on how I would get 3 years per row? I have 15 years of data that I would like put across 5 different rows.

Appreciate your help

The template has a parameter called Years Per Row (or something like that). It's set to 10 by default, but you can change it to 3 or whatever you like.

DeleteHi Ken,

ReplyDeleteComing accross this blog post only now as I'm working on a timeline! I was first thinking to go with a background image like in this viz by Yvan Fornes:https://public.tableau.com/profile/yvan.fornes#!/vizhome/CostofHealthbyCountry/Top39countrieshealthsystem but I must say the solution you and David designed is much more convenient for what I'm trying to do. Thanks for sharing this amazing resource!

Oh that's great to hear, Jade. Please be sure to share what you create (if you can, of course). I'd love to see it.

DeleteWhat's the formula for the Y Spacing field?

ReplyDeleteIt's a parameter

DeleteHow do you get the first year at the top? Also, for some reason I'm not calculating first year and last year correctly. Any tips?

ReplyDeleteI'd probably need to see your data and workbook to be sure. Any chance you could contact me via email? flerlagekr@gmail.com

DeleteI will reach out to you via email. I'm using your timeline to do a viz of Disney animated movies, starting with Snow White and ending up with Incredibles 2. I have a more interesting problem. How do I display a tool tip showing the movie released when there are more than one per year?

DeleteJust responded to your email!

DeleteHey Ken,

ReplyDeleteThanks for working on this. The dashboard design is just beautiful. I have tweaked it for my use case where I am using the dashboard to show Month Year over 5 years. I think it is a good way to show progress of a program or major milestones in a program.

Thanks,

Manoj

i can't open the workbook. is there any other way to get the template workbook

ReplyDeletei can't open the workbook. is there any other way to get the template workbook. Thank you!

ReplyDeleteWhat error are you getting? What version of Tableau are you using?

DeleteTableau 10.5

DeleteSend me an email at flerlagekr@gmail.com and I can send you a version of the template in 10.5 format.

DeleteBTW, I want to change year to date, what should I do in my case?

ReplyDeleteI typically suggest tricking Tableau into thinking it's a year. So match each date with a year in your data. For instance, link January 1, 2019 with 2000, January 2 with 2001, etc. Then build the chart using the year but display tht actual date in the tooltips. Does that make sense?

DeleteIn your suggestion, I should set years per row to 365 to show the entire year in date. Is that right? The exact graphic I want to make is just like the example of Guardian Graphic. A year length of days per row and I can show one or more specific events happen in the same particular date. I did email you personally on Feb. 27th with attachement of my dataset. Hope you can give me more detailed instruction. Thank you!

Delete