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.







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

39 comments:

  1. Hi Ken!

    This 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  2. Hi Ken,
    Coming 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!

    ReplyDelete
    Replies
    1. 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.

      Delete
  3. What's the formula for the Y Spacing field?

    ReplyDelete
  4. How 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?

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

      Delete
    2. I 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?

      Delete
  5. Hey Ken,

    Thanks 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

    ReplyDelete
    Replies
    1. hey Manoj, how did you do that?

      Delete
    2. If you can email me with some sample data, I'd be happy to show you. flerlagekr@gmail.com

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

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

    ReplyDelete
    Replies
    1. What error are you getting? What version of Tableau are you using?

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

      Delete
  8. BTW, I want to change year to date, what should I do in my case?

    ReplyDelete
    Replies
    1. I 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?

      Delete
    2. In 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
  9. Hi,
    This 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

    ReplyDelete
    Replies
    1. 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

      Delete
  10. Hi Ken,

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

    ReplyDelete
    Replies
    1. 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.

      Delete
  11. Hola 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.

    ReplyDelete
    Replies
    1. I 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?

      Delete
  12. Hi Ken, Love your work and enjoyed your presentation at Tableau Conf 2019.

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

    ReplyDelete
    Replies
    1. 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
  13. Thank you for all your work and your site helped me a lot! Just posted my #firsttableaupublic dashboard. https://public.tableau.com/profile/marife.domanski#!/vizhome/MarifeDomanskiWorkStory/MarifeResumeOverview

    ReplyDelete
  14. Thanks Ken for the blog. I finally published my tableau public viz on the history of ICC Cricket World Cup.

    ReplyDelete
  15. Hi Ken,

    I am having a same requirement, but all the events happened on a day and in every point we need to show a string, i have dropped you a mail regarding the requirements. Can you please help me ?

    ReplyDelete
  16. Hi Ken, Thank you for your post, it really helps me. But I have a question, my timeline is going backward... I have something like :
    2022 2023 2024 2025
    2021 2020 2019 2018
    2014 2015 2016 2017
    2013 2012 2011 2010
    Any ideas why? Thank you!

    ReplyDelete
    Replies
    1. Hmm, I'd have to assume it's some sort of sorting problem. Can you email me? flerlagekr@gmail.com

      Delete
  17. Hi Ken, is it possible to share the full data of this Tableau Workbook?

    ReplyDelete
    Replies
    1. The Excel template is here: https://docs.google.com/spreadsheets/d/1HfRtMGFpaHTLz9m6vCoMX9xvBFx9ZV-J/edit#gid=301201831

      Delete
  18. Hi :) Is it possible to create this for a single year view? with months?

    ReplyDelete

Powered by Blogger.