This weeks #makeovermonday data set was sourced from RDW, a Dutch open mobility data website. The data contained extensive details about car registrations in the Netherlands dating back to the 1950’s.
The Original Viz
The original visualisation using this data set (if you can call it that) was sourced from a 2016 news article posted on the Dutch Telegraph newspaper website which focused on car exports in 2015:
What don’t I like it?
- You will notice that this is simply a list! Although there is nothing wrong with the design the data could have been displayed in a more insightful and engaging way (i.e. a bar chart).
- There is a wealth of data avaliable in the RDW datasource which could have been utilised to support the story and to add further visualisations to the article to support the story.
- The focus of this list is very limited (exports in 2015 only) and could have benefitted from looking at the trends in export volumes over time.
I have admired the daily vizzes published by The Economist on their Daily Charts website / Twitter account for some time. They have produced a number of charts similar to this one recently which I had been hoping to replicate once I found a suitable dataset (it’s pure coincidence that this also focused on car registrations in Europe):
I felt this data source could be used to easily replicate a viz similar to the above so I thought I would attempt it.
What I Did
In order to produce the circle chart I needed to calculate the year over year (YoY) percentage change in car registrations (from 2015 to 2016 since 2017 is not a complete year). I knew once I had this calculation I could apply it as neccessary. However, this was easier said than done! I’m by no means an expert when it comes to calculations in Tableau and I must admit I struggled to get my head around this one. While I calculated the YoY percentage change in a table I could not re-use this calculation in isolation and apply it in the way I needed to due to the way Tableau works. Rather than give up and produce a different viz I decided to search for a blog post or video online that would help me to find a solution to my problem.
This is the beauty of Tableau and the community; you can almost guarantee somebody else has experienced the same challenge as you at some point so there’s a high probability the solution is documented somewhere, it’s just a case of finding it!
VizWiz – Data Viz Done Right
When I’m stuck with a Tableau question the first place I consult is usually Andy Kriebel’s blog, VizWiz.com. If you don’t already follow Andy’s blog I suggest you do so NOW! Andy frequently posts new tutorial videos, tips and examples of data viz best practice on his blog and he has been doing so for years so there is a treasure trove of information avaliable. In my case I found a video Andy had posted back in 2016 for #TableauTipTuesday which included the perfect solution to my problem. I’ve included the video below.
Using LOD’s to calculate year over year changes
LOD’s (Level of Detail calculations) are perfect in this scenario as they will always return the latest year in your dataset. However, there are a couple of steps you need to complete before getting to the stage where you can write the actual YoY calculation.
Note: All of the steps and calculations included below are covered in Andy’s video. This is purely showing how I adapted and applied them to build my viz. All the credit is Andy’s, not mine!
Step 1: Calculate the Latest Year
I started by adding the year of the date registered to rows and the number of records to the text shelf:
Next I wrote a simple LOD calculation to find the latest year:
Notice there is nothing at the beginning of this LOD (before MAX). This means that Tableau will scan the entire dataset to find the latest year.
Next I dragged the calculated field from the measures to the dimension pane and then added to the rows shelf to the right of the Year pill. You will notice the latest year is then displayed on every row (in my case 2017) all the way down the worksheet. However, I didn’t want to do a comparion of 2016 vs. 2017 because this would not have been accurate (2017 is not a complete year yet). Instead I wanted to compare 2015 with 2016. How could I make this work? Well I figured if I added -1 to the end of the calculation the LOD should return the year before the latest year (2016). I tried it and it worked! Woo hoo!
Step 2: Calculate the latest year registrations
Now I had the calculation for latest year I used it to calculate the number of cars registered in the latest year (or 2016 in my case) using an IF statement and the Latest Year calculated field:
When dragged into the view you will notice that the latest year registrations are only displayed against the latest year (2016). All other years show as zero which is correct (because these are not the latest years):
Step 3: Calculate the prior year
Now I had the latest year calculation (step 1) it was simple enough to calculate the prior year. Using a similar method to the year -1 logic I applied to return 2016 in the latest year calculation I duplicated the ‘Latest Year’ calculation and simply replaced the -1 at the end with -2 (obviously this would be -1 if you really wanted to calculate the prior year registrations). This calculation returns the year prior to the latest year (in my case 2015):
When dragged in the view this will show the prior year against every row:
Step 4: Calculate the prior year registrations…(almost there!)
The prior year registrations calculation is very similar to the latest year registrations calculation I created in step 2. I simply duplicated the ‘Latest Year Registrations’ calculation and replaced [Latest Year] with [Prior Year] like so:
When dragged into the view this will display the number of registrations against the prior year only (2015). All other years show as zero which is correct (because these are not the prior year):
Step 5: Calculate the year over year percentage change (finally!)
This is the final piece of the jigsaw!
Now I had calculations for the latest year registrations and prior year registrations I could use them to calculate the % change in registrations YoY. This calculation is as follows:
After creating the calculation I changed the default number format to a percentage with 1 decimal place. This will ensure that the value is always displayed correctly. This calculation is dynamic so if I were to update my viz with more data at a later stage it would always take the latest/prior year at that particular time (i.e. in my case since I’m looking at 2015-16 currently it would automatically change to calculate the values for 2016-17 once we move into 2018).
When dragged into the view I can see there was a 31.9% increase in car registrations in 2016 compared to 2015:
Now I was finally in a position to use my new YoY calculation in a chart!
To create the circle chart I added the new YoY change calculation to the columns shelf, changed the mark type to circles then I added the Sum of the latest year registrations to size. By doing this it enabled me to show the top sellers in 2016 as well as the cars which grew or declined the most in popularity YoY at the same time:
In my final viz I produced two versions of this chart; one to show the position by car brand and the other to show the position by car model.
Why stop there?
One of the reasons I love Andy’s Tableau tutorial videos is that he usually throws something additonal in for free; whether that be an extra tip or trick or an additional use case. In this instance he showed how you could go one step further and use the YoY % change calculation on a reference line in a bar chart and also how to create a data-driven alert. I thought I would utilise these in my viz also.
YoY Reference Lines
After creating a horizontal bar chart using the latest year registrations calculation I added a reference line using the SUM of prior year registrations as the value at cell level. This uses a the reference line to display where registration volumes were for the same car brand/model in the previous year so you can easily see which cars have seen an increase or reduction in registration volumes YoY. In the case of Dutch cars there were very few of the major brands that saw a decline in registrations from 2015 to 2016:
You can also create a data-driven alert to display a flag/circle/any icon of your choice next to any brands that have declined in registrations YoY. You will notice the red circle against Mitsubishi in the example above; this is the alert in action.
To create an alert you first need to source your desired ASCII icon. In his video, Andy used the www.jrgraphix.net website which has a vast selection of symbols to choose from. Once you have found your desired symbol, copy it from the website of your choice and paste it into the following calculation in between asterix’s, i.e. ‘symbol’. In my example below I have used a circle:
This calculated field can be added to the view (on the rows shelf) and in this case will display a red dot next to the bar where the YoY change in registrations for a particular car brand or model drops below zero; in other words the current years’ value is less than the prior years’ value, otherwise no symbol will be displayed. The symbol can also be formatted (in the same way you would format text) to change to colour, size, etc.
In my example I created two bar charts which both featured reference lines and data-driven alerts. The latest year/prior year calculations can also be utilised in tooltips to make them dynamic.
Here is my completed viz. Click on the picture to view the interactive version in Tableau Public.
Thanks for reading and a special thanks to Andy as this viz wouldn’t have been possible without the help of his blog!