# How I Created This Spiral Chart…and Why I Won’t Do It Again

Two months ago, I shared my viz, TORNADO.
Although the entire viz was well received by the community, the spiral
chart at the top garnered the most attention.
Many people commented that they had never seen this chart before, asked how to create it and if I planned to write a blog post
on it. I had previously seen people create spiral charts with circles (Neil Richards will be mentioned below), but not with bars and especially not with equal width bars (more to come on that).

Since I posted this viz, others have created this chart and written about it as well. However, in this blog post, I will talk about my original inspiration for this chart, the
math, the challenge, the iterations, and the result. And I will focus quite a bit of time on the biggest challenge - what made this different from other spiral charts - the equal width bars.

I will say the one and only reason that I created this chart was for the challenge…and because I absolutely love playing with Tableau. The result was a beautiful chart that was fairly useless analytically.

I will say the one and only reason that I created this chart was for the challenge…and because I absolutely love playing with Tableau. The result was a beautiful chart that was fairly useless analytically.

__Why?__
For Christmas, my lovely wife bought me the

*The Book of Circles*. This book included hundreds of beautiful circular visualizations and infographics. Each page contained incredibly beautiful works of art and I spent an hour on Christmas morning paging through and studying each one.
One particular graphic caught my eye.
The chart was created by Christian Tominski and Heidrum Schumann and showed
the temperature fluctuations in Rockstock, Germany between 2008 and 2014. It was a spiral chart, which I’ve seen before
(and created before), but this one appeared to have equidistant points on the
spiral. Unfortunately, I cannot find a
good image of the chart, but the following PDF documents their work. The middle chart at the top is very similar to
the chart in the Book of Circles, but the chart in the book was much larger.
(As a side note, it wasn’t until I finished my tornado
viz that I realized the points were not equidistant at all – more to come).

The math behind this intrigued me and over the next couple of weeks, I
couldn’t get it out of my brain (yeah, I’m weird). Therefore, I challenged myself to create this
spiraling bar chart with equidistant points (which means equal size bars) in
Tableau. To be honest, it was just a
challenge and I really didn’t know if the chart would ever be useful in any
situation.

__The Process__
I started with a proof of concept.
I utilized Superstore data to plot weekly sales per year in a radial
fashion. To do this, I utilized trigonometry
- generally the same steps I used to build a radial bar chart in the Flerlage Twins TC19 presentation
(it would be good to watch the entire video to gain a full understanding
of the math, but if you are interested in just the radial bar chart, it starts
at 16:19). Each week was represented by a point on a
circle and each circle represented a separate year. As you can see, each year utilized a
different radius.

You can see how the points got further apart as the radius
increased. In fact, the points in the
inner ring were almost on top of each other.

Rather than to have individual rings for each year, I next modified the chart to create a
spiral by simply increasing the radius.
I had done this before…I actually did it one month after starting Tableau
by structuring the data in Excel. I
created Kramer’s lollipop where each point on the lollipop was a word in the
script. Click on the image to go to the viz and hover the lollipop to read the entire script of this episode.

This time around, I adjusted my Superstore weekly sales chart to be a spiral (and of course, did all the calculations within Tableau). I incrementally increased the radius with each week, rather than use a constant radius for each year like before. I then set each year to be its own color. (As a side note, after creating this visualization, I learned that Neil Richards had written about how to create this chart last year. It's a great blog post - check it out here).

Okay, I was making progress, but I wanted bars, not points.
In order to do so, I would need four points for each week…one for each
corner of each bar. I’m not going to go
into a ton of detail here, but the math behind creating these bars is the same
as creating a radial bar chart like in our TC19 presentation, except that you do
it twice. So I calculated the points on
the circle (referred to as inner ring at TC19), then I duplicated my data and
calculated the second point (on the outer ring) which extends out from the
inner point based on my measure. Then I connected
the two points with a line. Below is an
image from our TC19 presentation that shows the inner and outer points.

However, in my case, I needed four points, so I essentially did the same
thing a second time, except this time, I offset the points slightly. I did two more unions (for a total of four
sets of my data). I then calculated the
second set of points at the same angle as the

**, but added a buffer parameter value to back it off so it wasn’t right on top of that next point. The two new points were identical to the first two points, but they were offset by some small angle. Below is a visual example.**__next point on the circle__
First, the original radial bar chart which consisted of two points and a line
(I’ve purposely made it very light in color):

Now I added in the second set of points connected by a line – again, these
were the same as the original two points, but offset by some angle (the line
connecting the additional points are in blue):

In the above image, the points connected by gray lines along with the next
set of points connected by blue lines all correspond to one value. Here is what the four points looked like with
the my actual data:

Then connected the dots with a line.

You can see that each week consisted of four points. These four points were essentially two radial
bars for each week, but connected together.
From here, I simply changed them to polygons.

When I looked at the chart, I saw exactly what I was trying to avoid. There were 52 bars (each representing a week)
on each year’s ring (full 360 degrees).
Each week was at the same angle on the ring as previous years, which was a good thing because you could easily see seasonality. However, if you looked at the image, you can
see that the bars on the outer ring (2019) were much wider than the bars on the inner
ring (2016). In my opinion, this was
quite deceiving as the

**represents sales, but the thinner inner bars gave the perception that they were also shorter. I knew that creating bars of the same width would have its own set of problems, but I was determined to figure it out how to do it.**__length__

__Equidistant Marks__
Okay, so how to would I create a spiral with points that were of equal
distant between them? Well, let’s review
what I did above.

I created 52 points on a circle, which I turned into bars. Each of the 52 bars would account for an
angle of 360/52 or ~7 degrees. No matter
the radius of the circle, each bar would cover 7 degrees.

However, what happens when the radius of the circle increases? Well, the circumference of a circle is
2πR. Let’s assume the radius of a circle
is 100. That means its circumference is
about 628. With a circumference of 628, the
width of each of our 52 bars is 628/52 or ~12.1. Now let’s double the radius for the second
circle to 200. The circumference of that
circle is 1256 and the width of each bar is 1256/52 or ~24.2 – double the first
ring. As we increased the radius, we
increased the circumference and we also increased the width of each bar.

In the above example, doubling the radius also doubled the width of the
bar. My goal was to have equidistant
points on the spiral,

**. In order to do that, I would need to adjust the angle of each mark as I increased the radius. Remember that as I doubled my radius, I doubled my width. In order to keep the width the same, I would have to divide the angle by 2 (in this case). So that is the math that I applied – I compared the length of the current point’s radius to the first point’s radius and took a ratio. In the example above, it would be 200/100 or 2. I then divided the increment of each angle by that ratio. In the above example, our radius was 100 and the total angle it covered was 7 degrees. In the second circle, the radius was 200 and our angle would be adjusted down to 3.5 degrees. This would create equidistant points on the spiral and equal width bars.**__which is the same as having equal width bars__
I applied this logic to a different data set (crude oil prices - I needed
more values) and set it up with just two points like a radial bar chart. It looked like the following:

That was pretty cool and as you could see, the points were equidistant from each other. This was exactly what I
wanted to accomplish, but the spiral…well, it spiraled out of control. I wanted the spirals to be tighter; the
radius seemed to increase far too quickly.

__What Now?__
Well, I was in a bit of a
conundrum. The angle used between each
point was being adjusted based on the radius, yet when doing so, the radius
increased too quickly. I was adjusting
the radius in a linear fashion – I was just adding a constant value to the
radius with each mark. Perhaps I needed
some other way to incrementally increase the radius…but I wasn’t sure how.

When I encountered this problem, it was Christmas time and Ken had just
came into town. I mentioned my dilemma to
him. There were kids everywhere and he
was quite distracted. He thought about
it for a few minutes, but didn’t have any solution come to mind. I forgot about it and figured I come back to
it when I had more time.

The next day, Ken and I talked about it again at a time where there were
less distractions…and I saw his eyes light up.
“Oh, I’ve helped someone do this before”. As soon as he said it, I knew what he was
talking about and in my head, I saw Soha Elgany’s incredible Journalist Death viz. (It’s a fantastic viz and I recommend you
check it out yourself). When Soha was
building this, she reached out to Ken about the very same issue I had. I don’t know all the details, but he had found some preexisting code that on StackOverflow
that showed how one might go about creating a spiral with equidistant
points. Ken had modified that code using
VBA and brought into this template (when you open the file, you can click File then choose
Download).

I’ll explain the template in more detail later, but this allowed me to
generate a list of x, y coordinates for equidistant points on a spiral…much
like the image shows on StackOverflow:

__The Actual Build__
I used the template to generate 30,000 points on spiral, far more than I
actually needed. In Excel, I lined up
those points with my crude oil prices data set.
I brought that into Tableau. I
then replicated the steps above to duplicate my data so that I had two sets, and
then created a spiral radial bar char that looked like the following:

I then did two more unions so that I had four sets of my data, offset the
angle, plotted all four points, and connected them with polygons that looked
like bars. I ended up inverting the bars
over a gray background and boom! It was
exactly what I was trying to do!

__Iterations__
Unfortunately, I found crude oil prices over time to be quite boring. Sure, oil prices have gone up, but for the
most part, it was just a factor of inflation.
I just didn’t like the data, so I tried to find something else. The challenge was finding something with a
lot of history.

I had previously fooled around with the daily Dow Jones averages so I tried
it using the same technique as above. Unfortunately,
it showed much the same - an increase over time. Therefore, I adjusted the measure value to look at daily changes, whether up or
down. This was the result:

The chart looked pretty darn cool, but it was far to sparse for my liking.

I wracked my brain for something that might work for this chart, but couldn't com up with anything exciting. I then mentioned it to my coworker and friend, Dinushki De Livera (we call her Dee). She said very plainly, "What about tornado data? Dang! Tornado data! That was the perfect idea! The spiral would fit nicely with the spiraling winds of a tornado. Now all I needed to do was find the data.

A quick Google search led me to a website that showed tornado data, but I couldn’t find any actual data that could be downloaded. I messaged the contact email on the page and
within an hour or so, they replied! They
sent me a direct link to the data - and the data stretched back as far as
1950! Woohoo! I found the data I needed.

Once again, I replicated the steps above, colored each decade differently, added
a background to provide some context and the result was as follows:

As I looked at this chart, I quickly realized a major weakness. The larger rings contained more bars than
smaller rings, which meant that seasons did not appear in the same areas on
each ring. It was very difficult to see
seasonality. I decided to create a separate
bar chart with a set action to allow users to select specific months of the
year to see trends. I also modified the
colors to highlight those selected months:

I sent this to Ken to get his opinion.
He absolutely loved the chart, but then said, “Are you going with those
colors?” Personally, I loved the colors,
but clearly, he did not. Do you want to
know the truth? I didn’t care. What does Ken know about data visualization
after all?!! I loved the colors and I
was going to keep them.

The next day, I showed it to Dee in order to get her thoughts. She also commented on how cool the chart was,
but then said, “Why did you choose those colors?” Darnit!
She hated them too. I guess I had
to at least investigate my color usage.

I searched the web for “good color combinations” and using those combos, I
built six more versions of the spiral chart.

I sent these examples out to a handful of people and the choice was
unanimous…they all loved the yellow and orange (above). I trusted their instincts over mine and went
with it. I’m glad I did. Thanks Dee, Kate, Brian, Lindsay, and Ken.

__Problems with the Chart__
I admit, I thought the chart was super cool, but there were a heck of a
lot of problems with it. Here is the
full chart:

1) It is hard to compare any sort of seasonality. In the image above, Apr, May, and June are
highlighted. You can see they are larger
than most other months, but it’s difficult to see due to the differing
locations on each ring. 2) It is very
difficult to compare a year to another year.
I think you can see that there were less

**tornados in the 50’s (inner part) than recently, but it’s difficult to really compare them like you could with a standard bar chart. I added the gray background (built the same way as the orange and yellow chart was) in order to add some additional context and allow for easier comparisons, but it falls short. And there are many other weaknesses that I could discuss as well.**__reported__
The truth is, I created this chart to test myself and because I love
fooling around with new ideas in Tableau.
This chart turned out very cool

**but very weak**__looking__**. Trying new things in Tableau is a great thing, you will learn a ton, but it’s also good to admit when something simply becomes data art and provides little benefit outside of data art. This is one of those cases. Perhaps there is some incredible use case for this, but it general…it’s probably best to stick to a standard bar chart.**__analytically__
That said, if you want to do this yourself, you most certainly can. Just download the Excel template from here. Download the file, then follow the instructions below.

1. Go to the Developer menu at the top.
If you don’t have the Developer menu, use these instructions to turn it on.

2. In the Developer menu, click on Visual Basic – it will open a new
window.

3. While in the Visual Basic Editor, open the Immediate Window by going to
View and selecting Immediate Window (or by pressing CTRL G).

4. Finally, in the Immediate Window, type the function name and
parameters. In this case, type:

*CalculateCoordinates2*followed by open parentheses, the number and points you want to generate, and closed parentheses. As an example, if you wanted 3000 points, you would type:*CalculateCoordinates2(3000)*. Then click Enter.
5. Close the VBA window and you should see an Excel spreadsheet containing
a list of points numbers, X value, Y value, and angle. (Note, before generating the points, you can
also modify your radius in the VBA code…just change it from 100 to whatever you
like. As a side note, I actually did my
spiral chart as a ratio to the whole rather than a flat value so that I did not
need to adjust this value).

Once you generate the points, you can apply them to your data then use the
steps above to duplicate your data, calculate points then create linear bars or
polygonal bars. I will, however, ask you
to please use caution with this particular chart.

So that’s how I created this spiral chart and that’s also

**. I hope you enjoyed the blog post and the viz. If you ever have any questions whatsoever, please feel free to reach out at any time.**__why I won’t do it again__
Kevin Flerlage, March 16, 2020

Hello Kevin,

ReplyDeleteI have downloaded the excel file but in the excel file is completely blank. Could you please share the link?

Yes, it will be blank when you first open it. If you follow the instructions in the blog post (right below the link to the file), it will populate the Excel sheet.

DeleteHello Kevin,

ReplyDeleteHow did you created the field Next X and Next Y? How can we know the number of points we had in our data? On which basis should join the data? Thanks for the blog.

In this case, the Next X and Next Y was calculated in the data source, within the spreadsheet, to avoid additional table calcs in Tableau. In regards to the number of points...I'm not sure how to answer that. I knew the number of points going in. And I don't apologize, but I don't understand your question about the join.

DeleteCan you please tell the calc for next x and next y? Thanks for help

ReplyDeleteIt's just done in Excel by setting the cell equal to the next row's data.

DeleteThanks Kevin.

DeleteThanks for the blog Kevin. Can you please tell us the formula of Next X and Next Y and how to calculate

ReplyDeleteit in excel?

For that part, I just made the NextX field equal to the X for the following line then copied it down. Very easy in Excel. In SQL, you could use LEAD and in Tableau, you'd have to use a Table Calc (like LOOKUP +1).

DeleteThanks Kevin.

ReplyDelete