So yesterday I finished off part 1 by outlining the aspects needed to create our coxcomb visualization and identify the points for the edge of our segment in order to do that we need to know:
- The angle
- The radius (SQRT([Measure])/((PI()*(1^2))/[Count])))
- The total number of segments
- Some mathematical understanding
So I will show you how I used Alteryx to allow me to do this. I will also remind you of the type of data input that we have. A series of dimensions and measures, pretty simple.
So the first question we need to ask is how many dimensions are in our dataset? We can use a summarize tool to do this. Creating a count distinct calculation of our dimension members. In our example this value will be 12.
We need to know this information for each of our dimensions to allow us to calculate our points. So to do this I am going to use the append tool to add this information as a column to the right of our original dataset.
At this stage it is also important to note that we may have multiple lines for the same dimension. So we should also summarize our original dataset to ensure our data is aggregated for each dimension member; grouping by our [Dimension Member] column and summing the [Measure] column. Of course you may wish to aggregate your data in a different way.
We now have a dataset that looks something like this…
Now I wanted to just add a RecordID to our segments, we will use this RecordID (which I renamed to SegmentID), to enable us to calculate our angles at each point).
Now in order to draw the points for each of our segments we need as a minimum 4 points as outlined with the image shown in my 1st post.
Of course realistically in order to create a smoother curve we should use more points. I used 13 in my workflow this will give us 11 points around the edge of our circle for each segment and our 2 (0,0) points.
Now in order to use the Path functionality of tableau our Path ID’s must be in one column on different rows. So if we want 13 Path ID’s for each segment we need to generate 12 additional rows for each. In Alteryx there is a tool for this, aptly named ‘Generate Rows’. In order to use this we must set an ‘initial expression’, ‘condition expression’ and ‘loop expression’.
In our case the configuration of this expression is pretty simple. Our initial expression is ‘1’, this is the value of our row for the dimension member that already exists.
Our loop expression represents how we want the PathID to change dependent on the previous value. We simply want to increment ours by 1, so PathID+1.
Our condition expression represents when we want Alteryx to stop generating rows. Well we want 13 rows in total, so on the 12th row, when we are incrementing by 1, the PathID will be 13. Therefore we can make our conditional expression PathID <= 13.
From here on in we just need one tool, the formula tool, in this we are going to create 5 different formulas which calculate the;
- angle in radians
We are almost there now, it is a case of calculating the angle at each point and the distance, with which we can then calculate the x,y point for each PathID.
Well remembering that PathID 1 and Path ID 13 represent our centre point for each SegmentID then we can just 0 these values. So,
IF [PathID] = 1 or [PathID] = 13 then 0
Unfortunately the next part isn’t quite as simple.
We need to first identify the increment for each PathID between 1 and 13.
Well (360/Number of Segments) will give us the angle of each segment, 30 degrees. There are 10 increments for each segment. Therefore, (360/Number of Segments)/10) gives us our incremental value.
We can also use our PathID to acknowledge the point of the incrimental proccess for each segment. I.e. PathID 2 should have no incrimental value as it is the first point; so we now know the incrimental value for each PathID is equal to;
(([PathID]-2)*(360/Number of Segments)/10)
We now just need to calculate the base value for each segment to which we add this incremental value. This base value is the starting angle for each segment, for SegmentID 1 we need a value 0 degrees, for SegmentID 2, we need a value of 30 degrees, 60 degrees for SegmentID 3 and so on.
This can be calculated as ((360/[Number of Segments])*([SegmentID]-1))
Using this information we can now build out our angle formula
[PathID] = 1 or [PathID] = 13
((360/[Number of Segments])*([SegmentID]-1))+ (([PathID]-2)*(360/[Number of Segments])/10)
Unfortunately for Alteryx users there is no Radians function so one final thing we need to do is change our angle into Radians using: Angle*(PI()/180)
So now to calculate our Radius for each segment. This is relatively straight forward when compared with the above. We just need our measure value for the specific segment, and the total number of segments, please do not ask me to explain the underlying math behind this, I have no clue:
SQRT([Measure]/((PI()*POW(1,2))/[Number of Segments]))
[Number of Segments])
probably makes it slightly easier to read.
We now finally have everything on our list!
- The angle – CHECK
- The radius (SQRT([Measure])/((PI()*(1^2))/[Count]))) – CHECK
- The total number of segments – CHECK
- Some mathematical understanding – CHECK
So lets now calculate our x,y values and export to Tableau!
(again remembering that PathID 1 and PathID 13 are 0,0)
x = if [PathID] = 1 or [PathID] = 13 then 0 else Sin([Radians])*[Radius] endif
y = if [PathID] = 1 or [PathID] = 13 then 0 else Cos([Radians])*[Radius] endif
Again I have made the workflow available so people can have a better look if they wish.
At this point it is simply a case of building your coxcomb chart in Tableau.
Of course with the Florence Nightingale visualisation I created there was a significant amount of formatting and I also had to layer three different outputs on top of each other to give me the three different causes of death, please reach out to me if you want to know more.