Using YoY change to colour shapes on a line chart

The final Makeover Monday of 2018 was on the average annual Christmas spending in the US. We were only provided with the year and the average spend for that year. Although it may seem simple, there were many different takes on this as you can see online. For my viz (see below), I used some custom shapes to produce a graph which consisted of some baubles hanging on a line chart. I decided to use the year on year change to colour the baubles, whilst leaving the line to show the general trend of Christmas spending. Although it only involved some dragging and dropping and a quick calculation, I wasn’t aware of how this was done before I started. However, after some playing around I found a way. Given some of my fellow DS12ers asked me to explain my method to them, I thought it would be best to share in this blog.  

The first step I took was to create a chart showing the YoY change, using a quick table calculation to show the difference from the previous year. Getting the calculation out of the view without having to rewrite it from scratch was the part which I puzzled over for a while. I eventually remembered that you can view the calculation behind a pill if you right click and select edit in shelf, which I then copied and pasted into a new calculation. Much to my annoyance, I have since learnt that you can simply drag your new calculated field pill over to your measures pane to add the calculation to your data.

Once I had this calculation to play with, I reverted my chart back to the normal data to show the general trend over time and turned it into a dual axis chart so I could attach the baubles to the line as a custom shape. At this point, my viz was almost how I had envisaged, but without the colourful baubles. I created a boolean calculation using my YoY change calc which read:

[YoY Table Calc] >= 0

This was then added to the colour shelf of the baubles to show whether there had been a YoY increase or decrease. A bit of formatting here and there and my viz was ready to go! Although simple, I had never used this trick before, nor did I know how to do so before I tried. I hope that, like me, you find this technique useful in your own work. The workbook can be downloaded from my Tableau Public for you to use. 

Leave a Reply