During Makeover Monday this week (week 22) I came across a problem: I needed to produce a Gantt chart for a huge amount of overlapping dates. Gantt was really the only way for me to go with start and end dates in the data (in the back of my head I’m thinking Mr Cotgrave will be loving this data given his fascination with the Chart of Bigraphy by Priestly) and I was fixated with showing the data in that way (I blame Andy) but everything I tried in Tableau left me frustrated.
Jittering left wide areas of open space and no room for labels, even if I zoomed into one area would render leave lots of the data unexposed.
I knew what I wanted to do…I wanted to neatly stack / fit the bars in a decent arrangement to optimise the space and show as much data as possible at the top of the viz. The original author in the link for the makeover had done this as such:
Now Makeover Monday usually has a self-imposed “rule” that I tend to adhere to, spend an hour or less (if I didn’t stick to this I could spend hours), but here I was after half an hour without any real inspiration except something I knew wasn’t possible in Tableau. It was a challenge and to hell with rules I do like a challenge – especially given the public holiday in the UK meant I had a little time.
So I turned to Alteryx, but how to stack the bars neatly.
Firstly I needed a clean data set, so I fixed some of the problems in the data with blank “To” dates and negative dates using a few formula and then I summarised the data to just give me a Name, From and To date for their life.
Algorithm-wise I think I wanted to create a bunch of discrete bins, or slots, for the data. Each slot would be filled as follows:
- Grab the earliest born person who hasn’t been assigned a slot
- Assign them to a slot
- Find the next person born after they die, and assign them to the same slot
- Repeat until present day
In theory this would fill up one line of the Gantt. Then I could start again with the remaining people.
An iterative macro would be needed because I would step through data, then perform a loop on the remainder. First though I realised I needed a scaffold dataset, as I needed all the years from the first person (3100BC to present day).
I used the Generate Rows tool to create a row per year, and then joined it to my Name, Birth, Year data to create a module that looked like:
I’d fill the “slot” variable in my iterative process. So next up my iterative macro.
Translating the above algorithm I came up with a series of multi-row formula:
The first multi-row formula would assign the first person in the dataset a counter, which would count down from their age. Once it hit zero it would stay at zero until a new person was born, at which time it would start counting down from their age.
The second multi-row formula would then look for counters that had started to work out who had been “assigned” in this slot and assign them the iteration number for the macro, i.e. first run would see everyone going in slot 1, second in slot 2, etc.
Perfect! Now to run it and attach the results to the original data:
Easy peasy Alteryx-squeezy. That took me 30 mins or so, really not a long time (but then I have been using Alteryx longer than you….practice makes perfect my friend).
So now back to Tableau:
Neat, progress! Look at how cool those fitted Gannt bars look. Now what….
Well I need to label each Gantt with the individuals name but to do that I really have to make my viz wide to give each one enough space….
The labelling above is on a dashboard the maximum 4000 wide…..we need wider! But how? Tableau won’t let me….
Let’s break out the XML (kids don’t try this at home). Opening up the .twb in Notepad and….
I changed the highlighted widths and low and behold back in Tableau – super wide!
Now I can label the points but what do I want to show – those Domain colours look garish….
So I highlighted Gender and….pop. Out came the women from history – nice story I think to myself. I decided not to add a commentary, what the viewer takes from it is up to them (for me I see very few women in comparison to men).
- I decided to reverse the axis show the latest data first and make the reader scroll right for the past, mainly I did this because the later data is more interesting
- I decided to zoom in at the top of the viz, generally I expect viewers won’t scroll down to the data below but while I toyed with removing it I decided that leaving it was a slightly better option. The top “slots” I’m showing are arbitrarily chosen but I feel this doesn’t spoil the story.
- I decided to add a parameter to highlight anything the user chose (Gender or Occupation) – tying it into the title too.
- I fixed AD / BC ont he axis using a custom format
So I spent a couple of hours in total on this, way more that I planned today but that’s what I love about Makeover Monday – it sets me challenges I’d never have had if I hadn’t been playing with the data. I’ve not seen this done in Tableau before so it was a fun challenge to set myself.
Click on the image below for the final viz