Select Page

Spray charts are great for visualising change or movement in two dimensions. As an example, the cricket wagon wheel is a classic spray chart. Every shot starts at the batsman’s crease and finishes where the ball was fielded.

For a graph with multiple lines going from A to B you need two rows of data with an x and y coordinate each. One row is for the start location and the next row for the end location. The table below shows the ideal  way for spray chart data to be stored. The “Ball” field identifies which line will be drawn, “Point” Identifies whether a location is the start or the end of a line and “X” and “Y” are the X and Y coordinates respectively.

If you want to create a spray chart with a single origin then you most likely have an end coordinate but no rows defining the origin for each point. To set this up you need two unused variables that you can pivot. In this example I am using the Superstore test data set and want to create an origin graph showing “Sales” and “Profit” by “Customer Name”. I arbitrarily selected “City” and “State” to be pivoted but it could have been any two variables that I wasn’t interested in for this visualisation. If you have no unused variables that you can pivot, then you will need to go back to your source data and append two dummy variable named like DummyA and DummyB that you can then pivot.

How to Pivot Data in Tableau

From this pivot, every row of data is essentially duplicated outside of the pivot fields themselves and within the “Pivot Field Names” field the first line has “City” and the second line has “State”. We now need to create two calculated fields that declare the x and y coordinates for the end points of our lines. At the “State” end of our line we will need the origin (0,0) and at the “City” end we will need the coordinates  ([Sales],[Profit]). To make this happen we can use two simple calculated fields:

Calculation1:

IF [Pivot Field Names] = “City” THEN [Sales] ELSE 0 END

Calculation2:

IF [Pivot Field Names] = “City” THEN [Profit] ELSE 0 END

This gives us Sales and Profit on our pivoted “City” rows and 0 for both calculations on our pivoted “State” rows. As is good practice with new fields, now is a good time to rename the fields something appropriate like “Sales Coordinates” and “Profit Coordinates” respectively.

Now if we drop “Sales Coordinates” and “Profit Coordinates” into Columns and Rows and “Customer Name” into Detail we get a scatter plot of our points. To turn these into spray lines we need to change Mark Types to Line and then drop Pivot Field Names into Path.