# 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

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

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

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

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

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!

DeleteHi,

ReplyDeleteThis is a very good curvy timeline chart. Can this be modified to show all the events that happened in a day? If so, can you give me suggestions on what I can change?

Thanks

Priya

Yes, you could. I typically recommend that you use the same data set but just add a new field for date. So the year would just be a fake year and you'd really be using the date. Does that makes sense? If not, feel free to email me and I can explain better. flerlagekr@gmail.com

DeleteHi Ken,

ReplyDeleteI want to ask why do we need "fixed" to calculate first year and last year? can i just use max([year]) to calculate last year? thanks!

Normal aggregations (such as MIN and MAX) will always work within the viz level of detail. So, if you have data from 1900 to 2000 and you're doing MIN(year), the result of this will be dependent on the row you're looking at. For example, if you're on a row where year is 1990, MIN(year) will be 1990. We need to isolate the first and last years of the data set and make them available for calculations, regardless of the viz level of detail. To do that, we have to use an LOD calculation. I hope that makes sense--this is difficult to explain.

DeleteHola Ken. Realmente lo que hacen es inspirador. Yo quiero realizar un calendario dÃa por dÃa del aÃ±o 2020. No alcanzo a comprender cÃ³mo armar en Excel la tabla que utilizarÃ© como fuente de datos. Me podrÃas ayudar con esto?. Saludos desde Argentina.

ReplyDeleteI don't speak Spanish, but using Google Translate, I think I understand what you're asking. Are you trying to create a timeline like this post or an actual calendar?

DeleteHi Ken, Love your work and enjoyed your presentation at Tableau Conf 2019.

ReplyDeleteI am trying to use this template to show 12 "year" dots per row for five rows. My intention is to treat the years as months, so showing five years, and then plot project milestones onto the viz. I adjusted the Excel chart to have 60 "years" (meaning 60 months for five years), and that worked fine, but when I adjust the parameter in the viz from 10 to 12 years per row, the timeline starts mid-row and ends mid row, instead of breaking down the 12 dots evenly across five rows. Thoughts on how to correct the start? Also, it seems like the Y Spacing parameter doesn't really do anything. Thanks!

I'd probably have to see it in order to determine how to adjust it. Any chance you could send me an email? flerlagekr@gmail.com

Delete