How to Sigmoid Bump Chart (Spline?)

If you follow me or my blog, you probably know that I am partial to the curve. I particularly love the sigmoid function, I don't know why, I just think it looks cool. One of the things I've been playing with lately is incorporating multiple sigmoid curves into the same chart. I've done this before in my Batman viz as a Parallel Coordinates with Smooth Transitions but I haven't had the chance to really unleash a sigmoid fully across a viz. The other day I was looking around and "bumped" into Matt Chamber's fantastic viz on the Car Color Revolution in North America. I was looking at it, and it hit me, I can use the Sigmoid curve between these points, giving it a smooth transition feel. Product below

I think it came out nicely, but I had to use a few tricks to make it work.

The how

First things first, as I always do, if you are not familiar with the Sigmoid Curve in Tableau, I recommend checking out Chris Love's tutorial on Sankeys. I'm sure he loves how much I reference it :)

In order to create the Sigmoid Curve, we need to define a [T]. For the Curve, this is a range of values from -6 to 6 among 49 points. So we need to generate 49 points for every record. There are a few ways you can do this, one is to use Chris' method of Unioning the Datasource and then leveraging Densification, the other is pre-processing the points and cross-joining it to the source. I chose the latter for this demonstration. So to begin, we need to look at the data.

As you can see, the data itself is not very complicated. It is simply one record for each Color and Year, and the Rank of that color. There is also another field Called Join, that has a value of 1 for every record. We need this so we can "Join" it to our Padded Table. See Below

By creating a Sheet with 49 points and a field with equal values, we can Join them in Tableau and create a cross-product of the results. This is how we create a Full Join, even though Tableau doesn't "support" it. Come 10.2 this will be made even easier because you will be able to create a Calculated Field in your Source, and join on that. That in combination with Cross Database join means we can full join tables with no backend interactions.

Once we have joined these in Tableau, we effectively multiply our dataset by a product of 49. Each record now has 49 points. So our next step is to calculate our [T]. This is actually a very basic calculation, in fact we could have pre-generated this in Excel.

[T] = ([Path]-25)/4

Next we need to generate consecutive points across the 16 years of data. To do this we calculate an index across the years. 

Year Order = [Year] - {MIN([Year])}

For this dataset (Beginning with year 2000) we could have stripped out the 2 years with a string parse, but I used this method to show that it can be done with any years, i.e. 1996-2014. This will effectively give our years a Range from 0-Max Year Count. We can now leverage this in a calculation to spit out our consecutive rows.

Path Order = (49*[YearOrder])+[Path]

Awesome, making progress. So we have our [T], and we have our consecutive marks, now for the hard part. In order to leverage the Sigmoid function we need to set a Start and End Value. The Start is easy, it is just our Rank. But the End Value is where it gets difficult. We need to figure out, for each color, what it Ranked in the next year. This isn't that big of a deal, except for the fact that added 49 records for every Color, Year combination, so we have to use Table Calcs.

Step 1 is to isolate the two points that change on a color. For us that is Point 49 (The next point would be 1, which is a new year, new rank).

[Rank 2 Setup] = 

IF MIN([Path]) = 49 THEN LOOKUP(MIN([Rank]),1) END

Here we are telling Tableau if the Path = 49, lookup the next rank in order, which would be 1, or our next year's rank. The key to this is have it restart every color.

Once we have this, we can now take that mark and make it repeat for each year and color. This will be our Rank 2

IFNULL(WINDOW_MIN([Rank 2 Setup]), MIN([Rank]))

Essentially what we are doing is taking our Next Rank for Color, and repeating it along the entire 49 points. This gives us a value we can use to create the Curve function.

Sweet, now we have our Rank, our Next Rank, and a consecutive path order, now we actually create the Curve.

[Sigmoid] = 1/(1+EXP(1)^-[T])

*Edit thaks to Alexander Mou for a more consice Sigmoid*
1/(1+EXP(-[T]))

[Curve] = MIN([Rank 1])+(([Rank 2] - MIN([Rank 1]))*MIN([Sigmoid]))

Now we can plot it!

Nice! But there is still a little work to do. Mainly, I really don't like that X Axis, it doesn't make sense we need that to be in Years. Lucky for us, Year is just number, so we can just transform our points back to the "Year" level.

[YearFake] = ([PathOrder]/49) + 2000

Basically we are just reversing the calc we did before and adding 2000 to it. This will change are marks to be back on a 2000-2015 scale, with many Decimal points between it.

Now, we don't want those pesky decimals to show up, and we don't want years 1999 or 2016 to show up, so we use a little formatting tricks to help us out. One Fix the Axis to ensure 1999 and 2016 never show up on the chart.

After that, we just set our axis tick marks to show every 1 point, eliminating the risk of a decimal showing up.

Once that is done, simply add a Dual Axis for Path 1, and do some cleanup, and then you get the final product!

Geesh, kinda a lot to do something not that complex. I wanted to show this off and how to do it because I really think it is visually pleasing. I love Bump Charts, and this isn't taking anything away from it, just adding a little flash. As demoed at TC16, Jump Lines are coming our way, maybe someday down the line they can add a Sigmoid/Spline option. I guess time will tell, but till then, I hope this tutorial helped.

Regards,
Rody