If you’re not following the InterWorks blog, you should be. They routinely crank out fantastic tips and tricks for Tableau. Dustin Wyers, a BI Analyst for InterWorks, recently wrote about “Creating Calendar Views in Tableau”.
Dustin’s post does an excellent job of taking you through creating a calendar viz step-by-step. But I felt it fell a bit short in the end since you didn’t actually see a calendar. Dustin’s end product looks like this:
Dustin’s post does an excellent job of taking you through creating a calendar viz step-by-step. But I felt it fell a bit short in the end since you didn’t actually see a calendar. Dustin’s end product looks like this:
I wanted something that looked more like a true calendar. I did so utilizing some of the techniques I outlined recently for creating a heat map, but also adding in some of the suggestions by Joe Mako. The end result, if you follow the tutorial below, will look like this:
Ok, let’s get to it.
Step 1 – Create a list of dates in Excel and open the data in Tableau.
Step 2 – Right-click drag the Date field onto the Column shelf and choose the MY(Date) discrete format
Your view should look like this:
Step 3 - Right-click drag the Date field onto the Column shelf and choose the WEEKDAY(Date) discrete format
Your view should look like this:
Step 4 - Right-click drag the Date field onto the Row shelf and choose the WEEK(Date) discrete format
Your view should look like this:
Step 5 – Clean up the view a bit by right-clicking on the Date label and choosing Hide Field Labels for Columns
Step 6 – Right-click on the WEEK(Date) pill on the Rows shelf and uncheck Show Header
Your view should look like this:
We’re getting close! But now you have to use a few tricks.
Step 7 – Right-click on the Date field in the Dimensions list and choose Duplicate
Step 8 - Right-click drag the Date (copy) field onto the Column shelf, choose the WEEKDAY(Date) discrete format and the resulting view should be this:
Step 9 - Right-click on the WEEKDATE(Date) pill (the middle pill) on the Columns shelf and uncheck Show Header
Step 10 - Right-click drag the Date (copy) field onto the Row shelf, choose the WEEK(Date) discrete format and the resulting view should be this:
Step 11 - Right-click on the WEEK(Date (copy)) pill on the Rows shelf and uncheck Show Header
Step 12 – Right-click drag the Date field onto the Text shelf and choose DAY(Date)
Step 13 – Align the Text to the top right, center the header for the WEEKDAY(Date (copy) field, re-size the view a bit and we now have a beautiful calendar.
Ok, sweet, we have something that looks exactly like a calendar, but so what? This doesn’t tell us anything.
Consider that you want to see the profit ratio for your sales (or any other metric) displayed on each day as a color. This is where data blending comes in handy.
Step 14 – Connect to the Superstore Sales data source (or any other data you want to use, as long as it has a date field)
You might ask at this point why I used a separate data source for the calendar dates when a Date field exists in the Superstore Sales data. The reason is quite simple. The Superstore Sales data does not have sales for every day. Therefore I will not see the Day labels on each of the boxes. For example, if there are no sales on January 13, 2009, then the 13 label on that day will be blank.
Step 15 – Create the data blending relationships. Go to the Data menu and choose Edit Relationships. You will need to create these Custom relationships:
Step 16 – Create a calculation for Profit Ratio by right-clicking on any field and choosing Create Calculated Field
Step 17 – Drag the Profit Ratio measure onto the Color shelf
Step 18 – Change the Mark Type to Square
Step 19 – Set the size to its maximum
That’s it! You must admit this is pretty cool.
Note that Tableau defaults positive and negative colors to the Red-Green palette; you might want to change it for those color blind folks out there (and to keep Steve Wexler off your back).
If you want to take it a couple steps farther, you could show the quick filter for the MY(Date) field, create a parameter to allow for any of several metrics to be chosen, and on an on to make it a truly interactive analysis.
In the version embedded below, I changed the color palette to Red-White-Green so that those days without any sales would be white instead of gray.
Try this with your own data. I bet your users will find this incredibly useful, especially as a performance monitoring tool.
What I especially like about this is the fact you could use it as a filter in a dashboard - have it up in the corner for people to sweep across ("Use as Filter" turned on).
ReplyDeleteIt would be great if you could do a join instead of the secondary source, but avoid the duplication of rows - I wonder if you could do that with custom SQL and some sort of union and select distinct?
Nicely done! I'll share that with my team, I think they'll appreciate it.
ReplyDeleteGreat idea for the date picker Alex!
ReplyDeleteAs for the duplication of rows, you have to do that in order to get the squares to fill completely with the color. Joe Mako showed me how to do this in lieu of creating a heat map.
Great tutorial. Very easy to follow and I love how it turned out! Thanks for sharing this.
ReplyDeleteI'm conducting four days of Tableau training for Tableau next week in NYC and I may just show this. But as you guessed, with a blue / orange palette. I like it very much.
ReplyDeleteSteve,
ReplyDeleteSometimes I leave it as red/green just because I know it annoys you. :-)
I showed the ATUG folks how to build this last week and it took under 30 minutes, and they're mostly beginners. It's super easy to train people on it and it got their minds thinking of the thousands of ways it could be useful.
Andy
That's pretty informative. I'd love to do a monthly calendar for myself too. Thanks! You're great.
ReplyDeleteI really like this layout. I can used it for a variety of the dashboards I'm creating, but I seem to have an issue with the number of weeks.
ReplyDeleteI can't seem to limit the number of weeks in the rows to 5. It also extends the columns out to display every single month in my data table.
I've gone through the steps several times and can't see where I missed.
Any thoughts?
Thanks,
Paul
Paul, you wouldn't want the number of rows for the weeks limited to 5 because sometimes you will need 6.
ReplyDeleteAs for the other issues, I can only know the problem if I can take a look at the workbook.
If it's something you can share, send it to kriebela at gmail.com.
I would also recommend you download the workbook in the blog post and looks at the setup of the worksheets. You might be able to track it down that way.
Excellent tutorial. Very very easy to follow, thank you for sharing it with us.
ReplyDeleteOne question , the company I'm working for, divides year in 13 periods for finance reason, a 13-period calendar splits the year into 13 four-week periods rather than 12 months of varying lengths, so I would like to ask if it is possible to use same technique to create a Period Calendar as you did with a Monthly Calendar.
Read more: How Do I Make a 13-Period Calendar? | eHow.com http://www.ehow.com/how_6884863_do-make-13_period-calendar_.html#ixzz1xFLfpsxb
Thank you again sharing the Monthly Calendar with us.
Jura, you should be able to apply this technique regardless of the dimensions you use. You could easily replace your period dimension for the date dimensions in my example.
ReplyDeleteI would suggest you download the workbook and see how it was built. You should be able to get exactly what you want if you duplicate the placement, format, etc, of the fields in the workbook.
How would you extend this technique to show the calendar for not just one month but for the whole year(e.g 3x4 matrix where each cell is a month)?
ReplyDeleteHow about adding a quarter of year field and a month of quarter field.
DeleteHaven't tried but it should give you the same dimensionality as with weeknumber and day of week.
And then add the month name like the date in the example.
//Peter