It’s your round at the bar…

Data

It’s your round at the bar…

by Dave Hart

In this post, drawing some circular inspiration from the front cover of David McCandless’ Information Is Beautiful, I’m going to take a simple bar chart and wrap it around a circle, for no other reason than to see if it can be done in Tableau. You can argue amongst yourselves as to whether this is a good/meaningful visualisation, but it’s a pretty little thing and has a few nice tricks in it so read it for its educational value rather than an integral part of your next client’s dashboard.

The Chart

We’re going to end up with something a bit like this. The finished visualisation is at the end of the post, complete with a few parameters to let you alter the appearance.

Overview

We will be using Tableau’s line drawing capabilities to draw our graph, so we will need two points for each bar, one positioned on an inner circle and one extending outwards along the same angle. Once we’ve worked out an angle for each of our bars we just dig out some school geometry to break that down into an X,Y plot and we’re pretty much done.

Drawing the green bar requires us to know the two radii and the angle on which the bar is projected.

Step 1: The Data

Normally on a scatter plot you are only plotting individual marks, so you have one row of data per point. However we need two points to define the line so we need to do a bit of work on the data connection. To do this we will use a UNION query to create a second version of the data. In order to keep the field calculations the same the second query will have a few strategically placed zeroes and will introduce a field called PathOrder which will be either a 0 or a 1 depending on whether it represents the start or the end of our lines. We will be using a cut down set of fields from the Sample Superstore (Subset) data source that comes with Tableau.

Connect to the Superstore Sales Subset excel spreadsheet and then use a Custom SQL connection as follows:

SELECT [Orders$].[Product Category] AS [Category],
[Orders$].[Product Name] AS [Item],
[Orders$].[Profit] AS [Profit],
[Orders$].[Row ID] AS [Row ID],
[Orders$].[Sales] AS [Sales],
1 AS PathOrder
FROM [Orders$]
UNION ALL
SELECT [Orders$].[Product Category] AS [Category],
[Orders$].[Product Name] AS [Item],
0 AS [Profit],
[Orders$].[Row ID] AS [Row ID],
0 AS [Sales],
0 AS PathOrder
FROM [Orders$]

Step 2: Table Calcs for Angles and Lengths
The first thing we are going to do is to work out the angle for each bar, and then work out a normalised length for each bar. We used a normalised length to make it easier to specify the size of the internal circle and allow us to fix our axes on the final chart. For the angle we should know that there are  radians in a full circle (360 degrees if that’s your thing). For the normalised length we are going to make the outer circle radius for the largest bar in the dataset equal to 1, so that means that each bar is going to be a fraction of the length between the two circles i.e. r2-r1

In my worksheet I am going to make the size of the internal circle a parameter called RADIAL_INNER set to 0.2 and the outer circle for the largest bar will be set to 1. I’m from a coding background though and don’t like “magic numbers” in code, so I’ll use a parameter called RADIAL_OUTER and set that to 1. With good naming conventions you can often get code to self-comment, and it gives you more flexibility if you ever wanted to change the value later.

Here are the calculated fields we need to create

RADIAL_FIELD:
[Sales]

RADIAL_ANGLE:
(INDEX()-1) * (1/WINDOW_COUNT(COUNT([RADIAL_FIELD]))) * 2 * PI()

RADIAL_NORMALISED_LENGTH:
[RADIAL_INNER]
+ IIF(ATTR([PathOrder]) = 0 , 0 ,
SUM([RADIAL_FIELD])/WINDOW_MAX(SUM([RADIAL_FIELD])) * ([RADIAL_OUTER]-[RADIAL_INNER]))

RADIAL_X
[RADIAL_NORMALISED_LENGTH] * COS([RADIAL_ANGLE])

RADIAL_Y
[RADIAL_NORMALISED_LENGTH] * SIN([RADIAL_ANGLE])

Step 3: Plotting the Cartesian Coordinates
We are going to have our chart sorted by Category and then by Item so we need to create a combined field. Click Category then Ctrl+Click Item and right-click and select Combine Fields.

  • Add RADIAL_X to Columns
  • Add RADIAL_Y to Rows
  • Select Line as the mark type on the Marks card
  • Add Category to Colour on the Marks card
  • Add our Combined Category&Item field to Detail on the Marks card
  • Add PathOrder to Path on the Marks card
  • For RADIAL_X and RADIAL_Y set the table calc to Compute By Category&Item
  • Edit each axis so that its range is fixed from -1 to 1
  • Format grid lines and axes as you wish

Step 4: Labelling and Parameters

Now we have our chart, the values are not as useful to the user as they have been normalised to help us set the inner circle, so we need the field labels on there (both on the chart and on the tooltips). If you drag the RADIAL_FIELD to Label on the marks card you will see that some labels for the 0 points appear inside the circle, to stop this from happening we create a calculated field that only shows labels above a certain threshold, which I will add using a parameter so it can be changed on dashboards as well as making the table calc self-comment


RADIAL_SELECTIVE_LABEL:
IIF(SUM([RADIAL_FIELD])>[RADIAL_SELECTIVE_LABEL_THRESHOLD], SUM([RADIAL_FIELD]), NULL)

Now all we need to do is put it on a dashboard and set up our parameters to let the user customise the graph.

  • Set the RADIAL_INNER parameter to have a Range of Allowable values from 0 to 1 step 0.1
  • Set the RADIAL_SELECTIVE_LABEL_THRESHOLD to 100000
  • Create a parameter RADIAL_FIELD_USE with available values ‘SALES’, ‘PROFIT’
  • Edit the RADIAL_FIELD calc to: CASE [RADIAL_FIELD_USE] WHEN "Sales" THEN [Sales] WHEN "Profit" THEN [Profit] END

Conclusion

Whilst it may not be the most informative of views it is a good example of how you can make Tableau draw visualisations that are a little out of the ordinary. Like I said at the beginning, sometimes you do things just to see if you can, so here’s the viz to prove it can be done! Hope you enjoyed the post, feel free to leave comments / ask questions below.

The Latest

Related

More About the Author

Dave Hart

Consultant

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • Dieses Feld dient zur Validierung und sollte nicht verändert werden.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK