The 20th airing of #MakeoverMonday in 2018 was a nice basic one, with just ten rows of data. As ever, the challenge was to improve upon the original visualisation.

In a rare moment of clarity, I decided that whilst I’d stick with the bars (this is me, after all), I’d convert the supplied metric into something a little more tangible. To me at least, knowing that drivers in London spend 74 hours in traffic jams over the course of 240 commuting days a year is a bit too abstract. How long are drivers actually stuck in congestion on each of those days, on average?

Metrics supplied were just the total hours spent in congestion, and then the somewhat arbitrary assumption of 240 commuting days per year (seems to be 5 working days multiplied by 52 weeks, less an assumed 20 days of annual leave).

OK – that gives us the basis of what we need. It’s at the first hurdle that the issue of time formatting crops up. I created a “Days” metric:

Screen Shot 2018-05-15 at 19.48.18

Which was pulled into a very basic calculation:

Screen Shot 2018-05-15 at 19.48.26

And highlighted my bugbear:

Screen Shot 2018-05-15 at 19.48.37

What is 0.31 of an hour? It isn’t 31 minutes. How can I make this more sensible? I worked out the minutes in a day stuck in congestion, but that didn’t really help:

Screen Shot 2018-05-15 at 19.56.23

It just gives me another decimal value:

Screen Shot 2018-05-15 at 19.56.29

18.50 minutes isn’t “a thing”. It is 18 minutes and 30 seconds, in the same way as 17.25 minutes is 17 minutes and 15 seconds. Hmm. What if we convert our minutes into seconds, and then divide that value by the total number of seconds in a day?

Screen Shot 2018-05-15 at 19.59.42

See what I did there? 🙂

The number of seconds in a day is just the 24 hours multiplied by 60 minutes, multiplied by the 60 seconds in each of those 60 minutes:

Screen Shot 2018-05-15 at 19.59.49

Dividing the [Seconds per day] calculated field by the [Seconds in a day] calculated field yields another decimal value:

Screen Shot 2018-05-15 at 20.01.56

So what? I’ve just gone from having 0.31 hours per day stuck in London traffic, to having 0.01285 (or 1.285% of the day). Still doesn’t mean much, does it? Not yet, but because this data only contains results where the elapsed times are less than a day, then I just need to do a sneaky bit of number formatting:

Screen Shot 2018-05-15 at 20.10.37

This minor tweak results in a more traditional means of displaying the passing of time:

Screen Shot 2018-05-15 at 20.10.58

I elaborated further, just to make it even more obvious:

Screen Shot 2018-05-15 at 20.12.53

Screen Shot 2018-05-15 at 20.12.59

The multiple calcs listed above aren’t needed in the final solution, and were just to illustrate the various stages of the thought process for this blog post. My actual calculation was:

Screen Shot 2018-05-15 at 20.19.21

Which is a curious mish-mash of logic and laziness. Ultimately, it formed part of the final visualisation, and I feel that the effort expended formatting time in this way reduces the cognitive load demanded of the viewer:

Screen Shot 2018-05-15 at 20.22.47

This is the Knowledge Base article I used for this #MakeoverMonday.