For week 32 of #MakeoverMonday, I created a dual-axis gantt chart which was well received, so I thought I’d write a quick post detailing how I created the viz. Previously I have written about the process of making a gantt chart in Tableau, but the approach this week was slightly different and so hopefully justifies a new post.

Here’s the viz in question – standard fare from me: one chart with an integrated title, and a couple of text boxes for a footer.

Screen Shot 2017-08-09 at 19.47.58.png

There are only a few of elements to this, so it won’t take long! Here’s a view of the worksheet, so you get an idea of the various filters in play, plus the basic architecture of the chart:

Screen Shot 2017-08-09 at 19.49.31.png

In terms of filters, I elected to focus on the usability of girls’ toilets only, as I was (still am) shocked by the incredibly low access to basic facilities in certain states in India. I’ve got two young daughters and the prospect of either having just a 25% chance of access to usable sanitation is hard to fathom. #MakeoverMonday does many things, including the heightening of your awareness of key challenges faced across the globe.

Also on Filters is a State (group) field, which aggregates together apparently duplicated states:

Screen Shot 2017-08-09 at 19.55.44

Here, I just chose to exclude states where data was not present for all three years; 2010, 2014 and 2016. I’m sure there was an automated way to do this, but I did it manually as there wasn’t much data to wade through:

Screen Shot 2017-08-09 at 19.57.13

So onto the structure of the viz itself. State (group) sits on Rows as I wanted to break the view up vertically so states could be compared easily. The main idea behind my viz was that I wanted to plot all the years for each state in an easily comparable view. I’d seen dot plots created this week which compared two values side-by-side, but I feel that my approach is more effective.

Plotting the years themselves is dead easy – you just stick Year on colour, and you’re done:

Screen Shot 2017-08-09 at 20.03.48

I know I’ll get three circles in each Row as I’ve filtered the data as described earlier. I’m always very muted when I use colour, so I selected a grey-black-red palette, specifically to draw attention to 2016 (red), so viewers can quickly appraise the current position, and whether that represents an improvement or a decline compared to prior years’ performance.

The most challenging aspect of the end-product was the gantt bar. Here you need to plot a start point for each state, and then a range over which the bar is plotted. I like this approach as it allows you to further contextualise the data shown by accentuating the absolute range of performance for each state. The start point leverages Level of Detail functionality:

Screen Shot 2017-08-09 at 20.08.49

In Kriebel parlance:

For each state and category, what is the lowest toilet usage value?

The next step is to work out the end point of the bar. If I know the start point (the lowest value), what is the end point? Well, it’s the highest value in the data. To represent that as an addition to the [Min], I need to plot the difference between the maximum and minimum values in the dataset for each state. I duplicated the [Min] calculation with a [Max] equivalent:

Screen Shot 2017-08-09 at 20.14.12

The pair worked together to plot the “range” of the bar:

Screen Shot 2017-08-09 at 20.14.21

Simple stuff. So you now just need to throw things together to create a cohesive gantt chart. First, put the [Min] calculation on Columns, and ensure that the chart type is set to Gantt Bar:

Screen Shot 2017-08-09 at 20.21.02.png

Second, put the [Range] calculation onto Size:

Screen Shot 2017-08-09 at 20.22.09.png

Excellent. Then we can simply rehash the Circle chart shown earlier on an axis:

Screen Shot 2017-08-09 at 20.24.54.png

You then create a dual-axis by right-clicking one of your green Pills:

Screen Shot 2017-08-09 at 20.25.37

Make sure you right-click one of the axes and Synchronize, to avoid misalignment of what is essentially the same metric:

Screen Shot 2017-08-09 at 20.26.33.png

Thereafter, there are just a few clean-up pointers. I set the gantt colour to a light grey so it was visible, but didn’t overpower the circles. I also fixed the range of the axis so the full 0-100% range could be viewed, because I wanted to further stress the fact that not one single state provisions 100% usable girls’ toilets.

In addition, I chose to sort the State (group) field in ascending order by the 2016 value, as I wanted to draw attention to the poorest performing state first:

Screen Shot 2017-08-09 at 20.30.18Screen Shot 2017-08-09 at 20.30.26

One final thing to note is the colour legend incorporated in the title:

Screen Shot 2017-08-09 at 20.32.55.png

I really like clean, clutter-free visualisations, and will always aim to free up some whitespace by not using a separate colour legend. There are no fancy calculations in my title, and the circles are simply ascii circles copied and pasted from this website, and then formatted as required.