At the mid-point of 2017, the #MakeoverMonday dataset looked at tourism in Berlin and Brandenburg. Akin to a bull in a china shop, I dived in, made some mistakes and was guilty of rushing things. I wasn’t alone in missing the inclusion of some sub-totals in the data, and I knew that I’d have to rework my original submission. Iterating is a good thing, because if there’s one thing that’s better than practise, it’s more practise.

Here’s a link to the original submission. It looks so-so, but I received some very useful feedback from the community.

Screen Shot 2017-07-03 at 19.15.57.png

  • What do the Big Ass Numbers refer to?
  • What Measure are you using?

The metric (Guests) is buried in the tooltips. It isn’t obvious, and therefore it’s an unnecessary hurdle for viewers of the viz to overcome. That problem, plus the underlying data issue meant that I needed to start again.

Having already made one submission, I got distracted by other submissions this week which themselves were inspired by vizzes from prior rounds of #MakeoverMonday. My favourite of those inspiring vizzes is by Rodrigo:


It’s a beautiful and effective chart, and this type of viz has grown in popularity recently, having initially been drawn to my attention in this post by Steve Wexler (where Steve credits Shine Pulikathara and Ben Jones with the discovery and evolution of this means of visualising data).

If I’m reworking a viz, I may as well make it a great use-case for a specific chart type, so let’s get going! I quite like a few aspects of my original submission, so I’ll try to retain the best of the old, with the best of the new. The reference point for the week is Mr. Kriebel, whose viz can be downloaded here.

The first thing I did was download a Google Sheet supplied by Sarah Bartlett, so I could join to the #MakeoverMonday dataset and translate German country names into English.

Screen Shot 2017-07-03 at 19.35.27.png

Step two is a simple MAKEDATE calculation:

Screen Shot 2017-07-03 at 19.48.54

So now the heatmap element of the viz can be created:

Screen Shot 2017-07-03 at 20.02.23

Super basic. Only notable things are the retention of my diverging black to yellow colour palette, and the inclusion of a filter to strip out some specific “countries”:

  • Abroad
  • Information Missing
  • Other African Countries
  • Other Asian Countries
  • Other European Countries
  • Other North American Countries
  • Other South American Countries

I’ve chosen to exclude them as they aren’t really specific enough to support any analysis. As I write these blogs whilst I build a viz, I’ve realised it will be preferable to exclude these as Data Source filters if I want to add interactive filters on the final dashboard…..

Next we need to start building out the horizontal and vertical bars to tack onto the top and side of the heatmap:

Screen Shot 2017-07-03 at 20.16.44.png

I’ve labeled things by using cell level reference lines:

Screen Shot 2017-07-03 at 20.18.49.png

When it comes to the construction of the dashboard itself, I need to be mindful that:

  1. I line the years and months up correctly!
  2. Spacing is going to be funky because as soon as I add the horizontal bars on the right, it will shift the heatmap and vertical bars about – I’ll have to pad things out

On the subject of padding, I immediately came across a problem – you just don’t have enough control when setting the height / width of tiled blank objects. It was especially obvious when trying to line the horizontal bars for years alongside the heatmap:

Screen Shot 2017-07-03 at 20.35.43

That’s a zoomed in view of the “Dec” of the heatmap, and a black horizontal bar for the total Guests in the year 2010. The outlined object is a blank object used to align the two charts. I’ve floated a text box shaded red and made it thin to show that the chart elements aren’t aligned. And they can’t be. No amount of tiled jiggery pokery will work, and that’s where the Floating gang will have a point. Is there another way? Another solution that gives me more control? Yep:

Screen Shot 2017-07-03 at 20.38.08

Still tiled, but look at the beautiful balance now! How is that achieved? Just a dummy blank field in the worksheet:

Screen Shot 2017-07-03 at 20.39.07

Headers are far more manipulable than objects, so if I now have a Header within an object, I gain more precise control over how things hang together:

Screen Shot 2017-07-03 at 20.40.26

That light blue shaded area shows the header within the chart, and I’m able to drag that up and down with more pixel precision than with a blank, tiled object. I still needed a blank object up in that top-right area as a Header can only be manipulated so much, and Andy Kriebel’s Marginal Histogram showed how to make use of that space this week – he put filters and a trend chart in that space:

Screen Shot 2017-07-03 at 20.43.47.png

The sparkline trend chart is a doddle to make:

Screen Shot 2017-07-03 at 20.58.34.png

A standard linear trendline is added and the axis does not run all the way to zero (as is permitted with a sparkline). The inclusion of my diverging colour palette somewhat masks the higher yellow months, so I ultimately reverted to black.

Is that it? Nope. Andy included a solution to the old issue of variable volumes. In some areas, the number of visitors was high and could be measured in hundreds of thousands. Some areas attracted fewer visitors, measuring only in tens of thousands. You need to find a way to tweak the number format depending on the scale of the area:

Screen Shot 2017-07-03 at 21.11.10

So if there are fewer than 1M Guests, invert the number of Guests, else leave it as it is. Why would you do that? Because you can set formatting based on whether a value is positive or negative:

Screen Shot 2017-07-03 at 21.12.36

In his viz, Andy used dual-axis bar / gantt combinations so he could use this new field on Labels in the gantt. That allowed him to put the values inside the end of the bar (which is why I originally used reference lines). I just plonked the new Measure onto detail and repointed my reference line at that field.

Didn’t work terribly well as the negative values were still presented as negatives, resulting in this (note where the labels are placed for the horizontal and vertical bars):

Screen Shot 2017-07-03 at 21.23.02.png

And so I went back, created dual-axis charts and dropped the new field on labels for the gantt. Gantts always make your axis go a bit wonky, so I needed to make sure that both the horizontal and vertical bar axes started at zero. My colour palette meant that I couldn’t get away with having the labels inside the bars, so I had to shove them onto the ends anyway! Another lesson learned.

All done! A great mistake to make again this week, and good solid practise based on another great viz. The workbook can be downloaded here.