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:
Which was pulled into a very basic calculation:
And highlighted my bugbear:
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:
It just gives me another decimal value:
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?
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:
Dividing the [Seconds per day] calculated field by the [Seconds in a day] calculated field yields another decimal value:
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:
This minor tweak results in a more traditional means of displaying the passing of time:
I elaborated further, just to make it even more obvious:
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:
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:
This is the Knowledge Base article I used for this #MakeoverMonday.