Can you excel at bar charts?

When Luke’s #WOW2020 challenge landed this week, I did a little happy dance inside, as I was pretty sure I was going to be able to crack this fairly quickly with minimal head scratching effort. This isn’t because I create these types of charts often, but because this type of side-by-side bar formed the basis of a #WorkoutWednesday challenge way back in 2017, and is a technique I can still recall – I didn’t even have to check my published workbook for a reminder… there’s just some techniques that just ‘stick’.

There may well be other ways to solve this challenge, but the technique I know is based on ‘normalising’ and ‘jittering’ dates, and is one I recall I picked up from Zen Master Jonathan Drummey’s Bars & Lines Blog post and associated workbook which can be downloaded from the blog.

So let’s gets started & hopefully, all will become clear.

Building the chart

In the dataset we’re using we have 4 years worth of orders, and need to plot the value of sales per month, per year in a bar chart which ‘groups’ the yearly bars for the same month together.

We’re going to plot the dates on a continuous axis (green pill), which typically would give a bar chart that looks like this, where Year(Order Date) is also added to the Colour shelf (Note I’ve also adjusted the colours to match the requirements).

We have 1 bar per month per year, but the months are ‘grouped’ in their sequential years.

So the first thing we want to do, is ‘normalise’ the dates as if they all occurred in the same year – any year is fine, I’m going to baseline them all to 2019

Date Normalised

MAKEDATE(2019,MONTH([Order Date]),DAY([Order Date]))

As you can see if plotting Order Date alongside Date Normalised this is simply transposing 03 Jan 2016 to 03 Jan 2019, 03 Jan 2017 would also be transposed to 03 Jan 2019

Replacing Order Date with Date Normalised on our initial chart gives us

But we want the bars side by side.

When using the automatic date hierarchy to plot dates at the month level, what Tableau is doing ‘under the bonnet’, is ‘truncating’ each date to the 1st of the month; so 3rd Jan 2019 and 18th Jan 2019 etc, are both actually plotted at 1st Jan 2019, and so on.

And when a bar chart is used to plot on a date axis, the left hand side of the bar is plotted at the 1st of the month point.

So with all this in mind, what we’re going to is ‘jitter’ the dates for each year to be clustered before the 1st of the month (for 2016 & 2017) and on/after 1st of the month (for 2018 & 2019).

Date Jitter

CASE YEAR([Order Date])
WHEN 2016 THEN DATEADD(‘day’,-9, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2017 THEN DATEADD(‘day’,-4, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2018 THEN DATEADD(‘day’,1, DATETRUNC(‘month’, [Date Normalised]))
WHEN 2019 THEN DATEADD(‘day’,6, DATETRUNC(‘month’, [Date Normalised]))
END

If the year of the original Order Date is 2016, then truncate the normalised (2019) version of the date to the 1st of the month, but subtract 9 days. So if the Order Date is 03 Jan 2016, then transpose it to 03 Jan 2019, then truncate to 1st of month, 01 Jan 2019, then subtract 9 days to 23 Dec 2018

03 Jan 2016 -> 03 Jan 2019 -> 01 Jan 2019 -> 23 Dec 2018

Depending on the year depends on whether dates are subtracted or added, and they have a suitable spacing between. The table below shows the relationship between the Order Date, the Normalised Date and the Date Jitter

Replacing Date Normalised with Date Jitter like for like, gives us the same view though, since it’s automatically rolled up to ‘month’

Change the Date Jitter to Exact Date

and ta-dah! you have your side-by-side chart. Each bar is being plotted at the exact date. If we just filter to look at May for example, we can see this clearer

The bars are obviously thinner than we want, so adjust the Size to be Fixed with a value of 4

To get the Tooltip to display the correct month and year, you need to add Order Date to the Detail shelf and change it to the discrete Month level (blue pill), then format the pill to display as an abbreviated month.

To get the axis to display an abbreviated month name, format that too and set to custom formatting of mmm.

Finally, to get the bottom axis and axis ticks to be darker, adjust the Columns Axis Ruler and Axis Ticks to be a solid dark line, then edit the axis, and delete the axis title

Your bar chart should be complete

Building the Legend

F0r the legend, I simply created a very simple existence chart using the circle mark type as below

And that’s it (once added to a dashboard of course!). The date jittering is a useful technique to be aware of – I’m pretty sure I’ve used it in other challenges too, but not necessarily for a side by side bar chart.

My published viz is here.

Note, my Date Normalised & Date Jitter fields in my published viz aren’t exactly as detailed above, because I decided to jitter then normalise originally, which meant I had to make an additional adjustment in my normalisation calculation. It’s only when typing this blog out and essentially rebuilding as I go, that I realised it was so much simpler the other way round!

Happy vizzin’! Stay safe!

Donna

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s