Turning Tableau into a Graphics Calculator, and oddness with Pi

As a learning experiment I wanted to see if I could get Tableau to work as a basic graphics calculator. While mostly for fun, some of the techniques are quite useful for interpolation of sparse data. I had been playing with making Tableau generate interim points for creating a Sankey chart of the general election seat changes (if you want to make these charts, Chris Love has a great post on them here). One of the tricks in making these is to use Tableau to generate bins with intervening points.

Initial Data SetStarting from a very simple text file containing a header and a numerical range, which will be used to define the number of rows in the dataset. imageNext step is to create some bins. Right click on the Row in the Dimensions shelf and click create Bins. I made the size of bins equal to 1, so I would end up generating a series from 0 to 10,000 consisting of 10,001 bins. Double click on the new field (Row (bin)) to add it to worksheet. By default only 2 rows (0 and 10,000) are shown. If you right click on the pill in the Rows shelf and select ‘Show Missing Values’, then voila the missing 9,999 values appear in the grid. So far so good.

Next want to convert this range into a percentage. As I need this as a sequence and the specific values specified in the file don’t matter, using the Index and Size table calculations to create this and fixing the calculation over the bins:

image

If I want to increase the number of points this can then be most easily be done by just reducing the size of the bins. This series will remain between 0 and 1. To make it slightly easier to change number of points, I added a BinSize parameter and changed the definition of the bins to be based off this.

Creating a Simple X versus Y Chart

Next step was to create a simple X variable. I added to parameter MinX and MaxX and defined X to be:

[MinX]+[N]*([MaxX]-[MinX])

Adding the parameters to the chart and adding X to the Columns shelf. As X is derived from N it picks up the calculation over the Row (bins). Drag the Row (bins) pill from the Rows shelf to the Detail shelf and we are nearly there. In the Columns shelf double click and enter an expression e.g. SIN(RADIANS([X])):

image

It is easy enough to use this technique to create dual axis plots or radial plots. Current workbook on Tableau public here.

Couple of oddities in formulae Pi(), Floor and Ceiling

Clearly this is a bit of an odd workaround to make Tableau plot this chart but found a couple of things out on table calculations.

Firstly the Pi function. I had an X value ranging from –360 to 360 and wanted to plot a Sin curve as above. Initial attempt was to do Sin([X]/180*Pi()). This didn’t work. It evaluated to null over all the points created by the bins rather than contained in the dataset. If you replace Pi() with it’s value then the formula evaluates fine. You can use the Radians function to convert from degrees to radians and this works fine with these bins. A little confused why the Pi function doesn’t work but easy enough to workaround.

Next functions I found I couldn’t use was Floor and Ceiling. This time the formula editor told me I couldn’t use them in table calculations. Again not quite sure why they have this limitation but easy enough to replace with the Int function:

FLOOR([X]) INT([X])
CEILING([X]) -INT(-[X])

Learnt a fair amount about table calculations and bins from doing this, though fun not sure how useful it is!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s