Not content with co-running #MakeoverMonday and all that entails, plus having a day job, Eva Murray has started writing for Forbes, and has been showcasing some #MakeoverMonday submissions.
In her article this week, Eva shared this visualisation by Marc Soares. Simple and effective, but sometimes simplicity is hard to replicate. Marc uses Google Data Studio to create his visualisations; can the same look be achieved in Tableau?
The challenge with recreating Marc’s viz is the positioning of the various elements. The country name, and rank will be easy enough, but how in Tableau can we position the currency values to the left of the chart, and then add the Year-on-Year data to the right of the chart?
Placeholders.
Let’s start up with the EU Superstore Sales data to build out the first parts, before we move onto what placeholders are, and how they can help us to match Marc’s design reasonably closely.
We know that Marc’s viz plots the percent change in a metric compared to a prior year. With the Superstore data, we’ll plot 2018 Sales and compare them to 2017 Sales. That needs a couple of Level of Detail calculations; one for each year:
Level of Detail is needed as we have row-level data, but we want to aggregate it up so that for each Country, we always return the SUM(Sales) where the Order Date year is 2018. The 2017 calc just substitutes “2018” for “2017”.
For the year-on-year comparison, I write my calculations like this:
And format the output like this:
That’s more or less it in terms of the calculations required. We just need one for the Country rank, and then another couple later on to help with the arrows and arrow colours.
The Rank calc just leverages the INDEX() function:
That’s enough calculation action for now; let’s start building things and see what issues arise as we go along. It’s an easy start:
A basic Top 10 filter on Country using our 2018 calculation helps to tidy things up a bit:
The first hurdle to overcome is all about data placement. Marc’s chart has the value of the equivalent of 2018 sales to the left of the bar. In Tableau, you can’t achieve this by using labels, as left-aligned ones just stay within the bars:
You also can’t edit the start point of the axis and try to use left-aligned cell level reference line labels, as this happens:
Enter the “placeholder”. This is just a method of plotting *something* in order to create an additional axis in Tableau with which to work. In effect, all we’re looking to do is create a uniform position on which to dump a label. We know we want the label to be to the left of the bars, so with the data in this instance only ever starting from zero, we know we need a negative value to “start” to the left of the bar.
To do this, we insert a value in MIN, MAX or AVG (in order to plot at a uniform point for each dimension member). The number you plot will determine where on the axis relative to the “real” data (i.e. the 2018 sales in this worksheet) your new placeholder sits.
Above shows the placeholder made into a dual-axis and synchronised. Note the “Abc” of the Text in a uniform column? Just whack your 2018 calculation here. After a bit of clean-up to remove headers and add a “2018 sales” header for the bar (literally just type “2018 Sales” into the Columns shelf and hide the Columns Field labels), we’re done for the first part:
The next stage is to plot the year-on-year section which has two components: the percentage change and a coloured arrow to represent an upward or downward trend. In isolation these are simple calculations. For the arrow:
I use this website to pick up unicode characters to use in my calculations. Colouring the arrows requires another calculation, using the SIGN() function:
This basically just plots negative values in one colour, positive values in another.
So far, so good. Time to plot these two elements onto the worksheet using placeholders again.
Hmm. There’s a lot of space between the bars and the new Text placeholders.
You can’t edit the axis to start from 0.25 so the space seems an issue. Does it persist when we drop our values and arrows onto the relevant section? Yes (note how I had to change the second Green pill value to -60000 from -47000 to “fit” the labels in:
I couldn’t figure out how to squeeze the white space here, so had to resort to a separate sheet for the arrow section:
When the dashboard was assembled, I just removed the right sided outer padding from the leftmost chart, and the left sided outer padding from the rightmost chart, to compress the white space as much as I could:
This has a far better balance, and whilst it’s slightly frustrating to not have been able to do it in a single worksheet, it’s no big deal. The workbook can be downloaded from Tableau Public here.
UPDATE
Donna Coles suggested an alternative way of trying one sheet, which specifically focused on the year-on-year bits. Donna’s proposal was that instead of using another dual-axis placeholder, I could use a single one, and modify my approach to the arrows.
Here, I’ve created separate arrow calculations:
These can then be used in the label for a single placeholder:
It’s a neat way of avoiding another dual-axis, but still results in a one-sheet approach with a vast chasm of white space:
UPDATE 2
Curtis Harris was next to the rescue:
Interesting. This flips things about a bit and my interpretation of the proposed solution resulted in a worksheet like this:
This uses some of Donna’s suggestion (the way of labelling the YoY section at the end), and some of Curtis’. I’ve just put a Discrete version of the 2018 Sales calculation on Rows, instead of using a placeholder. I lose some control over the Row labels as a result of this, but it’s a compromise that feels OK as it resolves the white-space challenges.
The dual-axis plots the same 2018 Sales metric alongside a new calculation:
{EXCLUDE} is used to remove Country from the scope of the calc, so it returns the greatest value overall (i.e. €308k for France). The *1.1 just creates space at the end of the second axis for the label to appear after the end of the bar.
Two great things about Tableau – so many ways to skin a cat, plus so much inspiration and support in the community. Here’s a link to the alternative version.
