Miguel Cisneros is one of those contributors to #MakeoverMonday who just can’t seem to put a foot wrong. His take on the data this week was unique and his wonderful visualisation really catches the eye:
Looks like we’re dealing with a couple of chart worksheets and one for the lower-left text box. Let’s go to work!
After downloading the workbook and unhiding the worksheets, I find four worksheets. I’ll work through them in the order they appear in the workbook, so first of all it is the horizontal bars on the left of the viz:
The filters are basically excluding Null values from the MONTH(Date Registered) Measure, and Nulls (or the incomplete year 2017) from the Year level of (Date Registered). The final filter limits the data to show only cars where the date of first admission in Holland is in the range 1971 to 2016. Then we have a Sort to shift the dates into descending order.
It’s key to note that Miguel elected to plot these horizontal bars in reverse. It’s an aesthetic choice to enable the separate chart elements to seamlessly be positioned back-to-back:
Inverting the absolute number of registrations is simply -SUM(Number of Records), but more work is required to identify the number of vehicles still registered with their original owners. The plotted Measure is:
So we need to check out that first orange field:
The premise here is that if the last year of registration is the same as the year the vehicle was admitted into Holland, then the ownership hasn’t changed in that period. So the initial [How Many With Original Owner] calc just plots the number of cases where this proves to be true. There are no colour calculations involved – each element of the dual-axis is just coloured independently:
A fairly sedate start. The second worksheet is the main component of the viz, and I don’t even know what to call it! I’ll temporarily label it a triangulation grid, as it’s similarly constructed to a few models I use at work. Here’s the structure in Tableau:
Note how simple things are in terms of pills on Rows and Columns – just one discrete blue pill on each. We’ve already explored the purpose of the filters, and the majority of action on the Marks card relates to tooltips, which I am not focusing on in this post. Let’s work to understand the core parts of this “triangulation grid”.
Straight away, I find something I haven’t knowingly encountered before:
Until now, I always assumed the “=” prefix to denote that these fields are calculations. They sort of are, but not in the conventional manner, because if I edit these, I see this:
What’s a Custom Date? In addition to that “official” Tableau link, here’s one of Robert Curtis‘ excellent Deep Dive posts, which focuses on this functionality. OK – so now we know about that “new” thing (for me at least), we can understand just what that blue pill on Columns represents (the Rows Measure is a conventional discrete Date).
Time to strip the chart right back to basics so I can see what’s what:
A square for each intersection of the two dates. If a car was first admitted into Holland in 2016, then it can only have been registered in 2016. If it was admitted in 2015, it could have been registered in that year, and it could have been subsequently re-registered in 2016. That relationship results in the triangle.
How do we sort the colours out then, what do they represent? They show dark orange where all of the cars remain registered with the original owner. If you put Number of Records on Colour, you get this:
We have contrast, but it isn’t pronounced and it’s also based on absolute values rather than the Percent of Total we want. Percent of Total? Sounds like a table calc?
The trick here is to manually set the Start and End points of the Colour scale via Edit Colours > Advanced:
A diverging palette ensures that you retain that phased contrast in the colour, and by fixing the End at “just” 10% it means you lose the washed out look of the view a couple of images further up the page, as a result of the way the Percent of Total calculations stack up:
Is that it? Nope! Further design flourish exists in the chart with a star symbol “alert” to draw attention to the most common registration year for each category:
This table calc plots the star symbol where the Percent of Total for the cell is the same as the highest Percent of Total for that Year of Most Recent Registration. But why does this work? It’s equally clever and simple, which sounds like an oxymoron:
The calc is on the Label, and that is formatted as required as ASCII symbols can just be formatted like any other text object. Simple and effective stuff.
Onto the dashboard itself soon enough, but there are two more sheets to tackle. Firstly, we have a simple sheet with Year(Date First Admission in Netherlands) on Rows:
This obviously retains the same filtering as the prior sheets for consistency. This sheet is used as a common y-axis between the horizontal bars and the “triangulation grid”. Initially, I couldn’t see the point of this – surely the Header from the “triangulation grid” could just be used? Not if you want to leverage a highlight action on the dashboard itself! Another ingenious little touch to separate this viz from the field:
The final sheet is the lower-left annotation box, which is a pretty standard affair:
Therein is just one calc we haven’t swept up so far, and it looks something like this:
So now we can finally look at the dashboard itself, which isn’t too daunting – hence the brevity of this section. It’s a combination of Tiled chart elements:
and Floated objects, for all of the text boxes and images:
And that is “it”. It’s a wonderful visualisation and we’ve already identified a couple of potential use cases for the “triangulation grid” at work which will blow the existing implementations (tables(!)) out of the water.
This is one of the main benefits of #MakeoverMonday for me – finding vizzes you love, taking them apart and then thinking of how you can adopt them in a work environment.