Makeover Monday: Week 46
Majority of Advertising Dollars are Now Spent Online
This is not a surprising fact, but the data looks interesting and I want to practice my visual animations with a racing bar chart
Connect to the data
This file is provided as an Excel file, simple enough
But once we import the data we run into some small issues that need to be fixed before we can start creating any visuals
1) The first row was not captured as the column headings
In Tableau Public, it took me a bit to figure out. It is hidden in the Sheet1 box.
2) The data was provided in a wide format, and needs to be transformed to a narrow format.
Not sure what wide vs. narrow tabular data is? Not a worry. Let us take a detour for explanation.
Wide (unstacked) data is presented with each data element in a different column. Our example, we have Type, Source, 2012, 2013, 2014, 2015, 2016......
Narrow (stacked) data is presented with one column for each containing all the values and another column listing the context of the value.
Back to our data, in order to transform our data into a narrow format, we use Pivot. The end result is the following.
I decided to share these tips in a short video tutorial. Enjoy!
Data Exploration
Now that we have our data ready to party, let's open a workbook and start exploring.
I typically start my exploration with a summary table to see what the aggregated data looks like. Here we see that the Types are general and the Source are more descriptive.
As you might have noticed, it appears that some of the Types are an aggregation (Media total and Total TV). I decided to filter these out in future analysis. To do this, you drag the 'Types' pill into the filters section on the left. If you right click and choose Edit Filter, you get the box below which you can make your filter selections.
I also decided to change the names of 'Total Magazines, Total Newspapers, and Total Radio at the Source. This didn't make sense to be as there was only one Source each within the different Types. To do this, you right click on the names within the table, and choose Edit Alias.
Then I created a couple of bar charts to visually see what the different Source and Type totals looked like for the 13 years. I like that the Source information give a little more detail as to where the advertising money is spent, but I also like the simplicity of the high level, Type, view.
Creating the Visual
Now for the fun part!
I want to create a racing bar chart because we have a time series data and I think animations are fun.
This might sound intimidating at first, but it is actually pretty simple.
1) Create a Calculated Field, you can name it Rank. RANK_UNIQUE is used on the SUM of the Total Spend. This returns a unique rank for the row in the partition and then sorts them in descending order so that the largest bars are at the top of the visual.
Note: there is also a way you configure it to be ascending if you have a need.
RANK_UNIQUE(expression, ['asc' | 'desc'])
Once you have your Rank pill, you can drag that into your Rows section. Also drag your Total Spend pill into the Columns section(it will change to SUM(Total Spend) and the column you want to measure into the Color Mark. I used Type as shown below.
Two things to configure on your Rank pill
1) Change from Continuous to Discrete (the pill will turn from green to blue)
2) Edit Table Calculation to Compute Using Specific Dimensions (I used Type)
You should now have a horizontal bar chart, with the bars sorted in descending order, largest at the top.
Animation Time!
This was way easier than I thought it was going to be. Basically, animations in Tableau are driven by pages. Since we want to animate based on the year, we would drag our Year pill into the Pages section on the left. Once you do that, a box will appear with the legend, which includes a dropdown to select the page (Year), a slider, playback controls, and show history.
The dropdown and slider are self explanatory.
The controls on the left are to move backwards, stop, and move forward.
The controls on the right are to adjust the speed of the animation.
The Show History is pretty neat if you want to show a trail of movement. That is not needed with these racing bar charts, so I will not cover details here.
I did end up creating another visual at the very last minute to include because I kept toggling between what level of detail I wanted to show (Type vs. Source). This enabled me to show both!
I chose to to create a treemap here which also was configured as an animation.
There you have it. Now it is all about putting together your dashboard and making things pretty 🦋
Here are some iterations of what I thought were final. I always think it is neat to see the progress of different projects.
👀 Check out my FINAL visual on my Tableau Public Profile here
Happy Learning!!
Not so Final, Final Versions
~ FIN