The GCloud Dashboard

Far and away my most popular dashboard is my GCloud dashboard, with over 4,000 clicks to date. It’s gone through various iterations over the last 18 months or so, but its primary offering has pretty much settled down into something that I think is flexible and summarises quite well the underlying data. It can be found here.

The GCloud framework was set up by government in 2012 as a way of disaggregating its IT provision. Its intent was to open up provision to a wider set of small and medium-sized enterprises, and become less reliant on the traditional big IT suppliers.

And as its name suggests, its intent was to bring about a much more cloud-based approach to IT provision.

The underlying data is published monthly (usually on around the 20-something of the month for the prior month) and can be found here.

The dashboard itself uses the feature that allows the user to choose their time unit for reporting. Otherwise, it’s a relatively basic presentation of the data. Perhaps it could do with another overhaul. But even so, I think it shows how GCloud has evolved, and gives the user the flexibility to drill down on any aspect.

My friend Alan posted earlier today his interpretation of the data here, which is well worth a read. It’s lovely to see someone presenting commentary based on cuts of data drawn from your own dashboard.

How this monkey discovered Tableau

I started working part-time as a contractor for a small public service company associated with the telecoms industry in January 2013. My role was to lead on all aspects of reporting – internal reporting to understand the overall health of our operations; and external reporting to our stakeholders and shareholders against formally measured KPIs and SLAs.

At first, I cobbled together some stuff in the world of Excel that I know and love. But it was clear that something more strategic would be needed to allow more dynamic, automated and strategic reporting. So we conducted an evaluation of three packages that at the time were all unknown to me: Tableau, Qlikview and MicroStrategy.

Tableau came out on top against our requirements, and so we bought it in May 2013.

Perhaps the most grating aspect of the product was its use of the terms rows and columns. In the spreadsheet world whence I’d come, these terms had a very different meaning to their meaning in the new Tableau world. (In some respects, “across” and “up” might be more suitable terms. Maybe that’s for another post.)

We had two days of dedicated training from the inimitable Emiliano. This was useful to a degree, but in some respects things hadn’t yet gelled with the product. As a newbie, the package was rather daunting, and perhaps some of the training was too advanced for us at the time, coming so soon after we had started using the package.

That said, if nothing else it gave us a huge insight into what the tool was capable of. We went live on Tableau 8.0 and went through a couple of minor upgrades before upgrading to version 9.0 in summer 2015. (Note to self: Must get round to upgrading to 9.2.)

I was doing a couple of days a week with this client, the other three days of my week being spent doing business analysis for a large bank: specifying data feeds into a billing system, and managing various migrations onto the new system.

But about a year ago, I heard that the bank was using Tableau for reporting, and so I threw my hat into the ring and developed a very rudimentary dashboard to show what might be possible to report on their business area. (My having now used the product for 18 months, I think they liked what they saw.)

And the rest is history. Nowadays, much of the time I spend with the bank is focused on developing and enhancing dashboards, introducing new elements, pushing the boundaries (at least my boundaries) of the software. I find it thoroughly rewarding. And I continue to use Tableau daily on the Telecoms contract, enhancing the existing offering andintroducing new stuff. Having recently implemented Alteryx there, I’m keen to use this to enhance the way in which data is presented to Tableau to make things more efficient and to take the offering to the next level.

For the last 27 years (yes, literally), whenever I’ve received some data, my automatic instinct has been to open my spreadsheet package of choice and play: Lotus 1-2-3 for five years, then a short period of Quattro Pro, and 21 years of Excel. Nowadays, when I receive some structured data, I invariably throw it into Tableau and try to make it sing.

My view is that working in Tableau with two clients – operating in completely different fields – makes me better. I encounter problems producing something for the bank that I learn how to solve, and these then benefit my telecoms client; and vice versa.

As for Tableau, I adore the software. I adore the company and its ethos. And I adore its community. And long may I continue to use it.

Aside

Polishing your dashboard: Tidy your tooltips and axis titles

If you want to polish your dashboard, make sure your tooltips and axis titles are tidy.

Tableau will always create default values for these, but these are often ugly, particularly if you’ve got table calculations going on.

Before you publish your dashboard, go into each sheet and check the titles of your axes. And make sure the structure of your tooltips makes sense from a user perspective.

I find that these two pieces are often overlooked. I’ve been known to overlook them myself.  Getting them right will likely pass your users by. But getting them wrong will ensure they don’t forget, for all the wrong reasons.

DATETRUNC by decade

I recently wrote a post describing how to allow users to decide the level at which their time-based data was displayed: year, quarter, month, week, day etc.

The solution uses the DATETRUNC function. However one weakness of this is that it does not allow decade-based reporting. The DATETRUNC function only goes as high as the YEAR unit.

This evening, I had a desire to allow the user to report at either year or decade level. So I set out to tackle the problem.

In the parameter field (described in the aforementioned post), I added the ability to select Decade. And I used the following formula to define the reporting field:

if [Date unit] = “decade” then str(year([Date])-(year([Date]) % 10))+”s”
elseif [Date unit] = “year” then str(year([Date]))
end

The initial clause is invoked when when Decade is selected. The last part, featuring the % symbol, is the equivalent of the MOD function in Excel. It divides the year of the date by 10 and brings back the remainder.

So if you have a date in the year 1987, it’ll do the calculation 1987 – 7, bringing back 1980. The “s” is strung onto the end to make “1980s.

If you need the field to appear as and be treated as a date, then things get a little more tricky. Unfortunately, you can’t use the DATETRUNC feature against the parameter, as whenever Decade is selected, it’ll break, even if that clause isn’t being invoked. (Rather annoying.) And so you’ll need to use an IF THEN ELSEIF to populate based on each of the parameter’s values:

IF [parameter] = “Week” then DATETRUNC(‘week’,[Date])

ELSEIF [parameter] = “Month” then DATETRUNC(‘month’,[Date])

ELSEIF [parameter] = “Year” then DATETRUNC(‘year’,[Date])

ELSEIF [parameter] = “Decade” then date(“01/01/”+str(year([Date])-(year([Date]) % 10)))

END

Not the most elegant. But it should do the trick.

UK Road Traffic Accidents: 1979 through 2014

Around 18 months ago, I found a dataset on data.gov.uk 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.

You can change the name of your “Number of Records” field

So in Tableau, whenever you create a new data source, it comes with an additional field that wasn’t in the data itself. This field is called Number of Records and appears in italics at the bottom of your measures.

If you right-click and Edit the field, it simply has a formula of 1. This means that every record takes the value 1, and so when you use this in dashboards, which you no doubt will, it acts as a record counter. When you do, any axis, tooltip etc. will get the name Number of Records.

But I discovered by accident and with utter delight that the name of this field is editable. So you can change the field to read Number of Accidents, Number of DealsNumber of Invoices, whatever your data represents. And this is delightful, as all of your axes and tooltips will have their labels changed.

Why it took me so long to discover this, I’ll never know. But now that I know it, it’s usually one of the first things I do.

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.

Solving the high-resolution usability issue in Tableau Desktop

This one has been driving me nuts for way too long. And today, I found a resource that solves this problem once and for all.

First, what’s the problem? Well, I have a Lenovo Yoga 3. And it comes with a lush 3200×1800 resolution screen. For the most part, it’s fabulous. But when I open Tableau, the user interface is horrible and unusable. Text in pills overlaps with the edges of the pills; the items in the panes on the left-hand side overlap with one another. The Show Me menu on the right-hand side almost invisible. And the pop-up messages, including the equation editor, are so small that they’re unusable.

But there’s a relatively simple one-time solution. And thanks to Dan Antonielli for helping out here. (He was having similar problems with the Adobe suite of products.)

Out of the box, Tableau informs Windows upon loading that it’s a DPI-aware product (DPI here meaning dots per inch). The solution makes Tableau think it’s not DPI-aware, which is basically the truth.

To solve the problem, we need to do two things. First, we need to create a new file in the system registry. And then we need to add a new file to the folder in which your Tableau executable sits. It sounds a bit scary, but it’s really not.

Here are the steps you need to follow:

First, creating a new entry in the registry:

  • Open the registry. You can do this by hitting Windows Button + R and typing regedit
  • Navigate to the following location: HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > Windows > CurrentVersion > SideBySide
  • Right-click the SideBySide folder and hit NEW > DWORD (32-bit) Value
  • In the new entry that appears, type the name PreferExternalManifest and hit Enter
  • Right-click the new entry and hit Modify
  • In the Value Data field, replace the 0 with a 1 and change the Base value to Decimal using the radio buttons
  • Hit OK and you can now close the registry.

Now we need to create a dummy executable in the Tableau folder:

  • First, download this file: tableaumanifest
  • Now move the file you just downloaded to the folder in which your Tableau Desktop executable sits. For Tableau 9, this is C:\Program Files\Tableau\Tableau 9.0\bin
  • Now change the name of the file to tableau.exe.manifest
  • If it gives a security warning, accept this.

Now, when you open Tableau, you’ll be presented with a delightful interface with fonts that look normal, pop-ups that you can read, and an interface that will let you create some magic.

Update: I did this originally on Windows 10. My computer just did an unprompted upgrade within version 10. And the fix stopped working. I was close to tears.

But apparently, the registry is overwritten with large upgrades, so you’ll need to redo that step if it stops working.

Happy days are here again.