Displays with Two Series

<---- Return to Main Page

This page has information about how to create a Nightingale coxcomb chart, in Tableau Public, having two series. First, we need to look at how Tableau Public needs to have the data organized.

When a two-series chart (aka graph) is created in Excel, the data are first arranged in three columns. the left hand column has the data labels, the middle column has numbers that I will call "Series 1", and the right-hand column has a series of numbers I will call "Series 2". Following is an image of what that table might look like for monthly data. I have selected random numbers for the data.

When Excel is used to set up a Tableau Public chart, the two series are organized one above the other as shown below. In column "C" there is a new series that identifies the the two series' as number 1 and number 2. I call them "Levels" You could use another word as long as you are consistent throughout.

The Excel images, above, may be graphed as lines using the Tableau Public setup at this link. However, that is not the goal. The goal is to create a Nightingale coxcomb chart. That discussion is continued below.

When creating the two-level coxcomb plot, start as you would with a one level chart and create the spreadsheet that is described elswhere in this site. Then make a second series exactly like the first, but with different values and different calculated values for x and y. Place one under the other. Do not add another set of header labels: the labels tht are in row 1 of the sheet segments shown above. Add a column called "Level" and enter numbers 1 or 2 to represent the two levels of the data. If you want to make three levels, add the third one below the second, and so on.

A complete set of two-level data for the Nightingale coxcomb plot may be found from a link on this page.

Now save the two levels of data into a single sheet, positioned with labels at the top and the top left postion in cell A1. In my example data, I call the sheet "Nightingale_orig_order_2_La 2." You may see that if you inspect one of the examples in Tableau Public.

Now open Tableau Public and connect to the spreadsheet.

Now set up the two level sheet as shown in the settings image shown below. This example is showing the source sheet as tab "Data2" in file "temp_2020_10_....." Y values are shown as columns, X values are as rows, "SUM(Level) is set to color, "Path1" is coded as a path, "Value1" is coded as detail, and 'MonthN" is coded as detail. The original image color settings were changed to blue for Level 1 and red for Level 2. It should work, but is not guaranteed to work. What I can say for sure is it worked for me.

It is possible, that your first Image of a Nightingale coxcomb chart will look like the following image. Notice how the red areas from the second level are very small near the center. Values in the top left quadrant are so small they can barely be seen.

When you compare that to Nightingale's original, it seems the blue section is too large and the red section is too small. That is because the size of the pie slices are based on the formula for a circle, with radius set to the values of variable "Value1". The Tableau Public then sets up the chart based on the largest values. The result is that the total area of the largest pie slice is over-stated. We can fix that problem by rescaling each value of "Value1" using the following formula.

NewValue1 = (Value1/3.141529265)^0.5. That is, we divide Value1 by the constant, pi, and then tke the square root of that result. The new value, shown here as "NewValue1" has a more appropriate size based on the total area of each slice. At this link, there is a complete list of the two series with Value1 rescaled by this method. The problem is that if you wish to have Tableau Public automatically show the value of the variable Value1 on the chart, then then a rescaled value will be shown and critics might think you are showing an incorrect value when in fact you have made the chart more appropriate. The correct values can be added as text boxes.

Below is what the rescaled image looks like in Tableau Public. It looks more like the original and the top left quadrant actually shows some values.