It’s a new year and while I’ve effectively been on hiatus from this blog for about 4 months it’s probably time to get started again.

We’ve been using Tableau at work for about three months during which I had something of a rare experience (or at least rare to me). Still being a neophyte with Tableau it was surprising to me that building a simple calendar view of data hadn’t occurred to the Tableau team. Purists will argue (and who’s to disagree) that calendars don’t make the best data visualizations (lack of ability to see trends, low data to ink ration, blah blah blah) but when you have a set of users that prefer a calendar AND everyone is already trained to use/read/interpret a calendar, it’s a compromise I can live with.

Long story short, it was easy enough to build the calendar framework in Tableau but I was still new enough to struggle with getting the data into the calendar in the way that I wanted. So, I reached out to the Sales Engineering team at Tableau (Thanks Ty!) and they he helped me out. The surprising part was the mini-storm of attention that this created on the Tableau team. Apparently this type of visualization hadn’t occurred to them and it created some excitement. What follows is a combination of what I learned on my own and what Ty helped me to create.

For this exercise, we’ll be relying on some of the training data that comes along with Tableau – the ’superstore data’.

This post is pretty in-depth (and long) but it assumes that you have a working knowledge of Tableau and SQL. If you are new to either or both, some of this may not make sense.

Set 1: Getting Set Up

1. Open Tableau
2. Start a new workbook
3. Connect to “Sample -Superstore Sales” Excel file

You may see some different dimensions and measures in here than what you see in your workbook. There are two reasons for this, there are a couple of custom dimensions and measures that we need to create along the way AND you may have a different version of this file than I do.

Step 2: Building the Calendar Framework

1. The first thing we need is a custom dimension, not because we’re going to use it right away but we will need it later for building in some interactivity and it’s part of the calendar framework. this dimension will be called “YYMM” and is a simple concatenation of the 2-digit year and 2-digit month from the Order Date dimension.

    

    

    

    

    

   This simple dimension is simple a string and will return values like ‘0801′ (e.g. January 2008)

2. Setting the stage…
   1. Right-mouse click and drag the “Order Date” dimension to the Columns pane. This should prompt you to select the type of aggregation to use – select “WEEKDAY(Order Date)”. This will get you your days of the week running across the top.
   2. Now drag “YYMM” to the Rows pane, followed by “YEAR(Order Date)” (which is the default aggregation for date dimensions), “MONTH(Order Date)” (remember that right-mouse drag?) and “WEEK(Order Date)”

      Right now you have a table that should look something like the following:

    

    

    

    

    

    

    

3. Making the table into a calendar
   1. In the “Marks” pane, change the Marks drop down to “Square”
   2. Make a new custom dimension called “Day” with the formula DAY([Order Date]) (Make sure that day is in the Dimensions pane and not the Measures pan after creating it)
   3. Drag “Day” to the “Level of Detail” box in the “Marks” pane

      Now the Tableau stage probably looks something like this:

      

       

       

       

       

       

       

       

   4. Let’s put in a quick filter so that we can look at just one month, we’ll remove the filter later. Control drag (CTRL+Drag) “YYMM” from the Columns pane to the “Filters” pane. Put a check mark on “Exclude” and then scroll to the end of the list and take the check mark off of “200812″. Now we’re just looking at December 2008. Still doesn’t look like much of a calendar does it? But it’s in there.
   5. We just have to manually adjust the size of the stage and the cells to get what we are looking for.
      1. Find the up/down handle on the Week of Order Date cell and drag it down so that the row height is about 1.5″
      2. Do the same for the Day of Week Columns. We’re now getting closer, you can probably start to see the calendar format:

          

          

          

          

          

          

          

          

      3. Add borders to enhance the calendar effect. Go to Format –> Borders
         1. Add a black, narrow border to Cell under the Default section of the Sheet–>Borders pane
         2. Ass an added bonus you can now fine tune your column width and row height to a square

         Your Tableau stage should now look an awful lot like a calendar, albeit an ugly blank one:

          

          

          

   6.  

       

Step 3: Adding Data and Visual Analysis Cues

So now we have our basic calendar structure but there is no data in it and let’s face it, it’s U-G-L-Y. Let’s start by adding data.

1. For the purpose of this exercise, we’re going to assume that we want to keep track of total sales (Gross Revenue) and Profit. We also need to add the day of the month to each cell. Initially, I had gone down the road of using Tableau annotations to create this but it’s cumbersome and breaks whenever the dates change. So the biggest thing that Ty in Sales Engineering helped me with was a custom measure that contained the Day of the Month, the total Sales and the Profit.
   1. So, we’re going to create a custom measure called “Day Calc” that concatenates the day of the month, the Sales total and the Profit total. The formula looks like this:

      str(MIN(DAY([Order Date])))+”

      s: “+str(round(SUM([Sales]),2))+”
      p: “+str(round(SUM([Profit]),2))

   2. Now, drag “Day Calc” from the Measures pane to the “Text” field in the Marks pane
   3. Right-mouse click a date cell and go to Format. Set the vertical alignment to “Top”. Horizontal alignment is your choice, as long as it’s left or right

       

       

       

       

       

       

       

       

       

       

       

      Ok, now we have actual data in the calendar, but what’s up with the color? The blue ain’t helping – maybe we should do something about that?

2. Use color as an indicator of health
   1. Drag “Sales” from the Measures pane to the “Color” field on the Marks pane
   2. In the new “Colors” pane that appears below Measures, click the down arrow and select “Edit Colors”
   3. In the new dialog box, change the palette from “Automatic” to “Red-Green Diverging
   4. Put a check on “Stepped Color” and change the number of steps to two (2)

       

       

       

       

       

       

       

       

       

       

       

      The colors, which are splitting on the average revenue in this scenario, are still quite a bit saturated though.

   5. Change the opacity of the cell colors
      1. Back in the Marks pane, drag the opacity slider under Color to the left to increase the transparency of the color in order to desaturate the display. Find a level you’re happy with it and go!

Step 4: Cleaning It Up

Now we have a calendar that gives us useful information and visual cues to the health of a particular day – hooray! But there is still a lot of stuff chart junk showing that we don’t necessarily need to show.

Things that can probably be hidden (not removed because they are either needed to maintain the calendar framework or we will need them later to create interactivity).

1. YYMM can be hidden
2. Week of Order Date can be hidden

   These two items can be hidden by clicking on the dropdown arrow in their respective lozenges (yes their called lozenges) in the Row shelf and taking the check mark off of “Show Header”

3. Additionally, we can remove the row headers. Right-mouse click on “Order Date” in the stage and select “Hide Field Labels for Columns” – Order Date should disappear. Right-mouse click on either “Year of Order Date” or “Month of Order Date” and select “Hide Field Labels for Rows” and both of those should now be hidden.
4. You may want to resize the Year and Month columns at this point but keep them visible because in the next post we’ll be making this calendar interactive so you want to make sure and indicate which month and year is being viewed
5. VIOLA! A calendar based view in Tableau:

Stay tuned for the next post in this series where we’ll discuss taking this view and making it interactive!

Tags: calendar view, Tableau, tableau-tips

This entry was posted on Wednesday, January 7th, 2009 at 8:33 pm and is filed under Tableau. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.