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?

Screenshot 2019-02-02 at 09.34.18

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:

Screenshot 2019-02-02 at 09.39.47

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:

Screenshot 2019-02-02 at 09.42.47

And format the output like this:

Screenshot 2019-02-02 at 09.43.31

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:

Screenshot 2019-02-02 at 09.45.04

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:

Screenshot 2019-02-02 at 09.47.10.png

A basic Top 10 filter on Country using our 2018 calculation helps to tidy things up a bit:

Screenshot 2019-02-02 at 09.47.47

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:

Screenshot 2019-02-02 at 09.51.22

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:

Screenshot 2019-02-02 at 09.53.00

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.

Screenshot 2019-02-02 at 09.58.32

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:

Screenshot 2019-02-02 at 10.00.37

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:

Screenshot 2019-02-02 at 10.07.49

I use this website to pick up unicode characters to use in my calculations. Colouring the arrows requires another calculation, using the SIGN() function:

Screenshot 2019-02-02 at 10.09.15

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.

Screenshot 2019-02-02 at 10.13.25

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:

Screenshot 2019-02-02 at 10.16.35.png

I couldn’t figure out how to squeeze the white space here, so had to resort to a separate sheet for the arrow section:

Screenshot 2019-02-02 at 10.18.28.png

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:

Screenshot 2019-02-02 at 10.19.39

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:

Screenshot 2019-02-02 at 12.26.18

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:

Screenshot 2019-02-02 at 12.26.29

UPDATE 2

Curtis Harris was next to the rescue:

Screenshot 2019-02-02 at 12.46.35

Interesting. This flips things about a bit and my interpretation of the proposed solution resulted in a worksheet like this:

Screenshot 2019-02-02 at 12.42.47

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:

Screenshot 2019-02-02 at 12.49.54

{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.

Advertisements