Curvy Bump Chart & Slope Chart Template

Curvy Bump Chart & Slope Chart Template

Edit Oct 1, 2020: about a year and a half after publishing this blog post, Julian Doublesin wrote a blog post to describe how he used Python to automate the process and make it easily reproducible.  Check his blog post out here!

A few weeks ago I created a visualization that looked at the NCAA Men’s Basketball top 25 rankings over the course of the season.  When I finished it, I was pretty proud of the result, which looked like the following:

However, I wasn’t a huge fan of the straight lines…they just looked jagged and unnatural.  So I decided to utilize curves instead – sigmoid curves to be exact.  As I wrote about in my NCAA Road to the Final Four blog post, I utilized Jeff Shaffer’s groundbreaking work using sigmoid curves (A Graph Recreation in Tableau and the follow-up regarding How to Build It) as well as a Sigmoid Bump Chart blog post from Rody Zachovichand Matt Chambers’ Path to the Playoff viz.  The result looked like the following:

In my opinion, this was a huge improvement.  It simply looked better than the alternative straight line bump chart.

I quickly realized that the same general workbook could be used for a slope chart as well (and many other charts). So, I decided that if I found it useful, perhaps others would as well…and that is the reason I decided to turn it into a template that could be utilized by anyone.

CHART UNDERSTANDING

Before we actually even download the template, I’d like to talk about understanding the chart and not relying fully on the template.  My brother, Ken Flerlage, has created templates for several charts (such as Sankey diagrams) and says the following in his Sankey Template blog post:

“Unfortunately, some chart types, including sankeys, can pretty difficult to create, even when you have a detailed tutorial. They tend to include data scaffolding, data densification, unorthodox joins, table calculations, trigonometry, etc. So when I create one of these, I always templatize them as much as possible. These templates typically consist of a relatively simple Excel spreadsheet into which some data can be plugged and a Tableau workbook, which simply connects to the spreadsheet. In the end, the process becomes relatively plug-and-play.

Before I move [on]…I want to take a moment to encourage you to drill into these chart types further. Part of the reason I am providing these templates is in hope that it will pique your interest in better understanding how they’re built. Taking apart complex charts is a really good way to learn some of the more advanced features of Tableau, so I highly encourage you to take some time to understand how these work under the covers.”

Well…what Ken said J.  I couldn’t say it any better.  I encourage you to read the blog posts from Jeff Shaffer and Rody Zachovich.  I encourage you to download Matt Chambers NCAA Football viz or my NCAA Basketball viz.  I even encourage you to look at the template itself to understand how these charts are being built. 

Okay, now onto the template!!!!!!!

DOWNLOAD

The template consists of an Excel workbook coupled with a Tableau workbook.  Both of these can be downloaded from my Google Drive folder.  Double-click on the Tableau workbook and select Download.  Then double-click on the Excel spreadsheet, go to File, then choose Download.  If you have any issues getting to the link, copy and paste the link below into your browser (Chrome is preferred). 

https://drive.google.com/drive/folders/1MCJrlqjenL1nzSrp5f9dndw56kJOK3db?usp=sharing_eip&ts=5c830c3e

DATA PREP IN EXCEL TEMPLATE

First, open the Excel spreadsheet.  It will contain three worksheets: Entry Sheet, Data Template, and Join Data.  The Entry Sheet should look like the following. 

The data that currently exists in the template is example data only.  You can delete this data, but do not delete the entire row…just the text itself.  (The reason for this is that the Data Template sheet will utilize every row to construct the data set.  Deleting rows in the Entry Sheet will create errors within the Data Template sheet).  Below is a breakdown of the columns:

Dimension: the “items” you are ranking over time. 

Time: an integer that must be sequential, which represents each time frame. 

Rank: the rank of each dimension within each time frame.

Measure: the value of each dimension within each time frame. 

Actual Time: this is your actual time frame that will be used as the x axis.

To start, we are going to focus only on the first time period (highlighted in yellow). 

First, enter your Dimensions for the first time period.  These could be countries, states, basketball teams, or anything else you would like to track.  Next enter an integer into the Time column.  It is recommended that you start with 1, but you can use any integer that you like.  If you have the actual ranking of your dimensions within that first time period, you can enter them in the Rank column.  If you do not know the ranks and you only have a measure to base them on, then you can leave Rank blank and fill out the Measure column.  When you fill out the measure column, the Data Template sheet will calculate the proper rank for you.  Finally, fill out the Actual Time.  This is the actual time frame you are measuring and will appear as the x axis in your chart.  This can be anything as long as you have a single value associated with each time integer.

As a side note, labeling the axis with the actual time is quite difficult.  The Actual Time will be displayed as what appears to be the x axis, but it isn’t an axis at all.  In order to display the proper information with the exact spacing, I used a LOD, some custom number trickery, and labels, then placed them in their own sheet (X Axis Label sheet). I won’t go into detail on this, but what is important is that what appears to be the x axis is really a label.  Please keep this in mind as you will not be able to edit it like a normal axis.

Now, let’s fill out the spreadsheet for the next time frame.  Enter the dimensions just like you did before.  For time, each subsequent time period must only show an increase of 1 – the numbers must be sequential integers.  In this case, we used 1 for the first time period, so you must use 2 for this time period.  If you used Rank for the first time period, do so again here.  If you used Measure, then use Measure.  Then fill out the actual time. 

Continue to fill out the template.  Remember to keep time as sequential integers. 

If you want to include additional information within the chart, you can add it.  In the Excel spreadsheet, go to the Data Template sheet.  This sheet builds a clean data set from the data on the Entry Sheet.  Please do not edit any of the values highlighted gray.  (As a side note, this spreadsheet allows for a maximum of 50,000 records – but that would be nearly impossible to display properly with a bump chart.  It should be plenty).  To add new data values, simply add that data to the right of the highlighted columns.  For example, in keeping with the basketball data, you could add a column for Win / Loss record.  Add anything you like, but do not modify the highlighted data or delete any rows. 

You do not need to do anything with the Join Data worksheet.  This will simply allow Tableau to create 49 points for every record in your data, which will ultimately allow Tableau to properly draw the curve. 

When finished, save and close the file.

TABLEAU TEMPLATE

Next, open the Tableau Template that you downloaded.  It may take a bit to load.  When it opens, it will open to a landing page that is nothing more than an image on a dashboard.  Click on the Data Source tab.  At the top, click the down arrow next to the Sigmoid Bump Chart Data source, choose Edit Connection, then connect to the Excel template you just edited and saved.  The workbook will take a bit to load.

In the data pane, you will see the Data Template joined with the Join Data.  This join is a cross join which will create 49 points for every record allowing us to draw the curves.  The links above discuss this in more depth. 

Click on Curvy Bump Chart Sheet worksheet.  You should see a bump chart using your data.  From here, you can format it however you like, add it to your dashboard, add tooltips or icons, add parameters for highlighting, etc.  As a side note, you may see some lines dangling off the end past your last point.  If this occurs, you can utilize the Path Order slide filter to trim back the lines as you like.  (Likewise, if your lines are cut off for some reason, you can slide that filter to include them).  

As mentioned previously, this uses a bit of a hacky technique to properly label the x axis with the actual time.  For that reason, the dashboard includes two charts, the bump chart and tiled below it, the x axis chart.  It is key that these are tiled so that the x axis lines up properly with the bump chart. 

And that’s all there is to it!!!!

ACTUAL VALUES

As a side note, you may want to create the bump chart using actual measurements versus ranks.  If you would like to do that, simply enter the actual measurements in the rank column (not the Measure column because rank will be calculated based on the Measure).  When you bring it into Tableau, the template is set to look at actual ranks with the Y axis showing rank 1 at the top, then below that would be rank 2, etc.  If using actual measurements, in most cases you would want the largest value to be at the top and the lowest value at the bottom.  To do this, simply right-click on the axis, choose Edit Axis, then uncheck the “Reversed” box.  This should put it into the proper order.  You’ll also want to alias the Rank field to something more appropriate.

ACTUAL USE CASE

I utilized this template for a recent Makeover Monday related to Philadelphia real estate transfers.  The data looked at the number of real estate transfers per zip code per year for 20 years.  I aggregated the data and brought it into my template. 

Using the chart, I was able to isolate a very interesting trend proving that the chart works.  I actually received a lot of response from many people in the community as well as several people that live in Philadelphia.  This chart did exactly what data visualization is supposed to do.  Check it out:  Forget 90210, Here Comes 19125. 

SLOPE CHART

This template could also be used for a slope chart as well since a rank slope chart is really nothing more than an abbreviated bump chart with only two time frames.  For this, you would utilize the template in the same manner as described for the bump chart. 

OTHER CHARTS

In reality, you can do a lot of things with this template.  It would be a bit hacky, but you could create a node link tree diagram using this template.  For the proper way to build this chart, see Jeff Shaffer’s blog post: Node-Link Tree Diagram in Tableau.

 

 You could even build an org chart or a curvy NCAA Tournament bracket (which I never found time to complete – maybe next year).  Albeit hacky, there are a lot of possibilities. 

This template is also available for viewing on Tableau Public.  View it here and if you like it, give it a Favorite to let me know.

Okay, there you have it, my first chart template.  I’d love to know your thoughts and as always, if you ever have any questions, please feel free to contact me via Twitter, LinkedIn, or via this website. 

Kevin Flerlage, March 26, 2019 | Twitter | LinkedIn | Tableau Public