Shipping containers: How and why

A few hours ago, Andy Kriebel posted his latest #MakeoverMonday dataset, information about the largest shipping companies in the world. I expect this was off the back of the bankruptcy of Hanjin, a situation that leaves over 500,000 containers stranded at sea.

I was in need of cheering up. Big time. And so I reached to Tableau a couple of hours ago to see what could be done. My mood was one seeking fun over integrity, and the result clearly showed this.

My first thought was to create some form of bar chart where the bars were containers. After a brief foray into this visualisation, it was clear that this would look rubbish.

I clicked on the dropdown in the Marks card to see what else might be of use. I saw the “Square” option, and got to work.

You see, shipping containers are rectangular in shape. (Technically, the Square mark should be called Rectangle. But that’s another post.) I imagined my visualisation instantly, and got on with creating it.

You can find the visualisation at the bottom of this post. Or else, click here.

There are three images in the background, which in hindsight could have been combined into a single image. (My image manipulation skills are woefully lacking, as evidenced by the use of PowerPoint as my image-editing-tool of choice.)

There is a PNG of the boat. There’s another PNG of the background (sea and sky). And at the top is a single-colour bar to go behind the parameters. This ensures that the shading of the parameters doesn’t look odd atop a vari-shade background.

Each of these is floating, which is quite apt given the subject matter. The boat is literally floating atop the water, in both senses.

And the visualisation itself is as simple as they get. A “Squares” chart, the size of each square driven by the quantity being displayed. A parameter allows the user to select what to chart; a calculated field uses this parameter to drive its own value:

CASE [Chart by]
WHEN “Cargo capacity (total TEU)” then [Total TEU]
WHEN “Cargo capacity (chartered TEU)” then [Chartered TEU]
WHEN “Cargo capacity (owned TEU)” then [Owned TEU]
WHEN “Ships (total)” then [Total Ships]
WHEN “Ships (chartered)” then [Chartered Ships]
WHEN “Ships (owned)” then [Owned Ships]

A second parameter allows the user to select how many companies to display, and this drives a “Top X” filter on the Company field.

Like the images, the worksheet floats, and it sits in front of all the rest to ensure that it hides the background. Placing it just above the ship gives the impression of shipping containers. Possibly.

I had a lot of fun with this one. The ability to select the Top X companies makes the visualisation statistically woeful. (The area chart should be a percentage of the total, not of some arbitrary subset.) I knew this, but this was kindly reinforced by Andy Cotgreave 😉

The Tableau community cheered me up no end this evening, something I so dearly needed. Nineteen likes on Twitter gave me a much needed boost. Thank you all! Hope you like it.

UK Road Traffic Accidents: 1979 through 2014

Around 18 months ago, I found a dataset on that contained details of all traffic accidents from 2005 through 2013. At the time, I created a dashboard that simply contained a colour-coded map, allowing you to see where each one took place. A tooltip gave some further details about each, and a rather ugly set of filters down the right-hand side allowed you to sub-select what was shown: by speed limit, severity, whether the police attended, year etc.

At the time, I was quite pleased with what I’d done. It brought the user closer to the data, and in some respects made the data sing.

Move on 18 months and I looked at the dashboard with disappointment. It was rubbish. It was one-dimensional. (Technically, two. It contained a map, with latitudes and longitudes. Little else.)

So I set out to improve it. And here is the result.

I’ve rebuilt it completely. I’ve created charts to show how things trend from year to year; how accidents are profiled throughout the hours of the day, days of the week and months of the year; and some pie charts showing the breakdown of accidents by severity and speed limit.

I’ve sourced data that takes things all the way back to 1979 (although I can’t map anything pre-2005), making a total of 7.8 million accidents to dissect and report against. Dropdowns allow you to select specific roads or severities of accident.

To save load time, I’ve made the police force field a single-select, allowing you to look at only one at a time. (You *can* select All, but you might want to go put the kettle on.)

And if I may say, I’m rather delighted with the result. Stay safe, and try not to contribute to the dataset.

Allowing the user to choose the time unit for reporting

One of the loveliest little things I’ve discovered in Tableau is a little technique to allow the user to select the time unit used for reporting: years, quarters, months, weeks, days etc.

I’ve used the technique on my #GCloud dashboard, which reports on the UK government’s use of its #GCloud framework for IT service provision. On the right-hand side at the top, you can use a slider to select months, quarters or years. (The data behind this one is presented at the month level, and so there’s no need to go down to weeks or days, but you could if you wanted to.)

To do this, create a new parameter. Let’s call it Time Unit. And make it a string, with a list of the time units that you want to use. In this case, monthquarteryear. Make sure they are all in lowercase (no leading capital). If you want them to appear with leading capitals in the dashboard, then add these as “Display As” names (e.g. Month, QuarterYear).

In your main dataset, let’s say you have a field called Sales Date which contains date values. Now, create a new field in your main dataset, called Reporting Period, say, and give it the following formula:

datetrunc([Time Unit],[Sales Date])

This will truncate, or “round down” your sales date based on the time unit selected in the parameter. So if you select Month in the parameter, your new field will round down your sales date to the first of the month; Quarter will round it down to the first of the quarter, etc.

And you can now use this new Reporting Period field instead of your Sales Date field in your chart. Right-click the pill and hit Exact Date; and right-click again and hit Discrete.

Add the parameter field to the view to allow the user to control it. I always like mine to be a slider, but you can choose to use radio buttons or a dropdown selection box. Whatever floats your boat.

Let me know how you get on!

The Periodic Tableau: How it was created

I was thinking up possible names for this new blog the other day, and paused for a while on the possibility of calling it The Periodic Tableau. Both a play on the periodic table, and an indication that updates abbout Tableau would be made periodically. It’s a name that I’m fond of. In the end, it didn’t stick. It wasn’t fun enough. Tableau Monkey won out.

But in coming up with the shortlisted name, I wondered whether it would be possible to go recreate the periodic table of elements using Tableau. And so I went to work. Here’s a link to the Viz; and below is a screenshot.

Periodic Tableau


First, the dataset. I cobbled the core attributes together in Excel from various internet sources: element names, symbols, element types, atomic numbers, atomic weights, melting and boiling points, electron configurations (whatever that may mean), even the year of discovery. There was one row for each element.

And I added some further data: a field for each of row and column number (based on the elements’ relative positioning on the table) and a link to the elements’ respective Wikipedia pages.

I also parsed the electron configuration into substrings, as some need to be presented as subscripts and some as regular text. So, for example, the element silver has an electron configuration of [Kr] 4d10 5s1. This I parsed into four fields:

  • Part 1: [Kr] 4d
  • Part 1 subscript: 10
  • Part 2: 5s
  • Part 2 subscript: 1

I also created some dummy records: a couple to create spacers between the logical elements of the table; and a bunch to create instructional text—the legend of types, an “example” entry near the top to indicate what each square contained and where, and the redirects to the lanthanide and actinide series of elements. All told, there were 136 data rows, 118 of which were the elements themselves.

I then used this as the data source in Tableau.

Once in Tableau, the core building blocks were pretty simple. My row field went in my Rows; column went in columns. I created a dummy field that I called “One”, which was merely the number 1 for all records. This I used to size the bar that appeared wherever a row/column combination was present. So each bar was equal in size, the size of the cell itself.

The type field defined the colour of the bars, and I upped their size to bring the rows closer together. I also manually changed the size of the rows and columns to make the viz look more like the periodic table we know and love.

The next step was to use the labels to put the relevant text into each of the bars. And it was this that took the most effort. I needed some artificial spacers in the header to separate the atomic numbers (top left) from the atomic weight (top right), while also keeping the atomic number locked for elements without an atomic weight. (It’s not possible to have a left-aligned element and a right-aligned element on the same row in labels or tooltips.)

Additional artistry and “fannying around” was needed to introduce a lower font size in the header of the “example” element than was used for the main elements, for reasons of space. It’s likely not something you’ll notice, but you’d have noticed it had I not done it.

And I went through a similar process in constructing the tooltips. And here’s where the parsed electron configuration fields were used. I strung them back together in the tooltip, but put the subscript fields in a lower font size—Tableau doesn’t yet allow for subscripts in tooltips.

There’s even a field that contains either an “n” or a blank” depending on whether the element type begins with a vowel to allow the tooltip to distinguish between a/an: an alkali metal; a transition metal.

Finally, I created a URL action (on menu) on the dashboard, allowing you to click through from the tooltip to the Wikipedia page about the element.

Here’s the link again to the viz on Tableau Public. Hope you like it.