I didn’t manage to finish my “What’ the Harm?” dashboard in time for #IronQuest or with any degree of polish, but I did manage to come up with a new (at least for me) chart type: the hex spiral unit chart.

I’m not sure it has that much data visualisation value, but it looks kind of pretty!

This article describes how to build one:

Background and alternative approaches

For my What’s the Harm dashboard, it was critical for me that each of the individual cases were visible and the case details could be shown in a tooltip, as the whole point of the dashboard was to make clear the personal impact that So-called Complementary and Alternative Medicine had had on individual people’s lives:

Aggregated charts were therefore out and I needed some kind of unit chart.

My first thought was to use a bubble chart, with each of the bubbles being the same size and representing just one case. However, I also wanted to use different icons (shapes) for each category of harm, but bubble charts only work with circles. As far as I could work out. Should you know differently, I’d love to hear from you! Shame, as the I liked the “blobbiness” of the bubble charts:

My next thought was to use word clouds based on single-character words based on unicode icons. There is a huge variety of unicode icons, but I wasn’t really satisfied with the icons or overall results:

Finding the code

So I started searching for the logic behind building bubble charts, but didn’t find anything I could use. Then I had the idea of hex charts and figured that there must be someone out there that had described how to build these. As I wanted to vary the number of units (cases) per chart, I thought it would be nice if it spiralled out from the centre. Bingo! After quite some searching, I found the perfect answer on StackOverflow from Tom Sirgedas.

To be honest, I couldn’t immediately interpret how the function works or even what language the script was written in, but I could read the logic well enough to be able to recreate it in Tableau. The script uses a variable, i, to designate each hexagon, 1, 2, 3, … I replaced this with an INDEX() calculation, [0. Index]. I’ll come back to the Compute Using aspect of this later.

[Index]

INDEX()-1

The -1 is needed to that the first dot is right in the centre. If you prefer a hole right in the middle, then leave this out.

The other calculated fields, in order, are as below.

[1. Layer]

INT (ROUND(SQRT(
[0. Index]
/3
)))

[2. FirstIdxInLayer]

3*[1. Layer]([1. Layer]-1)+1

[3. Side]

INT( ([0. Index]–[2. FirstIdxInLayer]) /[1. Layer])

[4. idx]

INT((
[0. Index]
–[2. FirstIdxInLayer] )%[1. Layer])

[x]

IF
[0. Index] //required so that the central point is not left empty
= 0 THEN 0
ELSE
([1. Layer]* COS(([3. Side]-1)PI()/3)+([4. idx ]+1)*COS(([3. Side]+1)*PI()/3))
END

[y]

IF
[0. Index]
= 0 THEN 0
ELSE
([1. Layer]* SIN(([3. Side]-1)*PI()/3)+([4. idx ]+1)*SIN(([3. Side]+1)*PI()/3))
END

As you can see, there are no references anywhere in the calculations above to any data. All that comes through the INDEX() function.

Building the chart

The example below is built from the Superstore database. Each dot in the chart represents one product.

Drag [Product Name] to details in a new sheet. This will give you a square for each of the 1850 products in the database.

Drag [0. Index] to the sheet, click on the pill’s dropdown, Edit Table Calculation, Specific Dimensions, Product Name. We’ll be adding to this later.

Drag [x] to Columns and [y] to Rows and set up their table calculations to match [0. Index] (right-click / edit table calculation / Specific Dimensions / [0. Index] and you have your basic hex spiral chart:

One can spice things up a bit with colours, icons, sizes, etc.

In this case, I’ve selected Mark Type as Shape and selected solid circles.

Dragging [Sub-Category] to Colour reduces the number of visible dots as there are now multiple marks on top of each other for each visible dot.

The table calculations (for Index, x and y) need to be updated to include Sub-Category:

Note that the order of the “Compute Using” fields makes a difference here. If you don’t place [Sub-Category] above [Product Name], the colours will be a bit of a mess as INDEX will be going along Product name then Sub-Category, not Sub-Category then Product as we want it. You could also achieve the same affect by going to the Product Name pill and selecting sort by field, Sub-Category.

I clicked on Colour and selected Hue Circle. This gives a nice, trippy rainbow effect:

We can add further graphical interest by using shape and / or size. For example, one could drag sales or quantity to Size to highlight the products that have been successful.

In this case, I’ve dragged [1. Layer] (which unsurprisingly indicates the layer within the chart on which a point lies) to size. This makes the dots in the middle smaller. Double click the pill to edit it and put a minus sign in front of [1. Layer] and you get the effect I was looking for.

As the whole point of using a unit chart was to be able to identify individual cases, or in this case, individual products, I’ve added sum of sales and units sold to the Tooltip. Getting rid of lines on the sheet and setting the background to black gives:

You can download the Workbook from Tableau Public here.

I’d be interested to hear from anyone that finds something useful to do with this chart type!