Let’s explore a chart type that’s likely familiar to most people: the heatmap calendar.

Now, this one is not new by any means. I personally learned these techniques from Andy Kriebel’s VizWiz blog way back when I still had a few hairs on my head and fewer inches on my waist. I’ll walk you through my build which will be near identical to Andy’s. However, I want to take this one step further.

I’m going to show you how to view an entire calendar year in a single view (single workbook) in a similar fashion to a 12-month wall calendar. When I presented the heatmap calendar build at TC16, I challenged all three audiences to come up with such a solution. And guess who figured it out: None other than my co-speaker, Ben Neville. Dude is good! How good? I’m pretty certain he sent me the response within an hour of our last session with the email subject line “Merry Christmas…not sure what to do with my afternoon now.”

Now, if my memory would have served me well, I had been holding onto the answer for several months, courtesy of Tableau extraordinaire and close friend Sean Trout. Sean had sent me an almost identical solution over the summer and I must have been too focused on sipping drinks with umbrellas to recall his great work. So kudos to both Sean and Ben. I will share both calculations (as there are likely several other ways to reach the same answer, but beware of performance).

Let’s start with the end in mind. Here’s what we’re after:


Common use cases:

  • When used as a selector on a dashboard
  • When “Daily” value AND “Day of Week” value are important
  • When viewing daily values for an entire year is important

Not ideal when:

  • Data needs to aggregated to the week, month, quarter, or year levels
  • When precision is important
  • When you want to see the speed or shape at which your data is trending, shifting

With this in mind, we might typically something like the daily change in the Dow Jones Index in the following way: (Data sourced from FRED Economic Data, Federal Reserved of St. Louis)

And this is good. It confirms that the market goes up and the market goes down, changing more often than the weather in North Carolina (last week we had a low of 0 and a high of 71 within a couple days. We have all four seasons some months).

But were there any months that were better than others? Were there any days of the week that were particularly stronger or weaker than others? This chart doesn’t help answer these questions. A calendar can—not to say a series of bar charts wouldn’t do the trick and do it well, but calendar can and a calendar may be more engaging than a series of bar charts.

So with that, here’s a simple build of a calendar with a couple clever calculations to help arrange the months in a format familiar to most. (and at the end, I will offer up more challenges).
Warm up your right mouse button. If you haven’t yet found the power of right-clicking and dragging objects onto your viz, you will today with your date fields. Try right-dragging in other data types as well—more magic.

For today’s build, let’s dive into the Dow Jones Index data visualized in the bar chart above. Since we’re dealing with Stock Market data, the weeks will only consist of five days since trading doesn’t occur over Saturday or Sunday.

Step 1: Build out a calendar

Once connected to the DIJA data set, let’s first build out a monthly calendar:

  1. Right-click and drag “Date” to the Columns shelf
  2. Select “WEEKDAY(Date)” from the discrete date parts
  3. Right-click and drag “Date” to the Rows shelf
  4. Select “WEEK(Date)” from the discrete date parts
  5. Right-click “WEEK(Date)” and uncheck “Show Header”
  6. Right-click and drag “Date” to the Filters shelf
  7. Select Month/Year from the discrete data parts and click “Next”
  8. Select any month, year (I’ll select June, 2016, a month most Brits will not soon forget)

Step 2: Add color

It’s starting to take shape but let’s add in some color for Daily Index Change

  1. Change the Marks type to “Square”
  2. Drag “Index Change” to the Color shelf

Step 3: Change color

While I love Tableau’s new default color palette, it likely alienates less folks with any form of red-green color blindness. When I’m analyzing financials data, I prefer to visualize in red and black. This just makes more sense to my brain so it resonates much quicker. Think “Back in Black” by AC/DC if you prefer.

  1. Click “Color” and select “Edit Colors”
  2. Change the Palette to “Red-Black Diverging” and click “OK”

Step 4: Format your viz

So now we’ve got some color but a little bit of formatting with go a long way to really make this look sharp.

  1. Right-click and drag “Date” to the Label shelf
  2. Select “DAY(Date)” from the discrete date parts
  3. Make the calendar a little larger by dragging the horizontal and vertical edges
  4. Click the Label shelf and change alignment to “Upper Right”
  5. Right-click “WEEKDAY(Date)” in the Rows shelf and select “Format”
  6. Under Header > Default > Dates > Select “Abbreviation”
  7. Click the Color shelf > click border > choose “White”
  8. Right-click open space and click “Format”
  9. Go to “Format Borders”
  10. Under Row Divider, change Pane to “None”
  11. Right-click on “Date” label above the days and select “Hide Field Labels for Columns”

This is great, if you only want to look at one month at a time, but what happens if you select more than 1 month?
Select all 12 months from 2016, and set the view to to “Fit Height.”

We get something that looks like this:

Even if we add month labels, it’s still going to be a mess. So let’s get that to an at-a-glance layout that has become unconsciously engrained in our brains.

Step 5: Create a calculation

We need to get each month to align to its corresponding column. I personally like the three-months-per-row approach since it gives an additional quarterly look at the data. But four per row seems to be a popular approach. We’ll go with the former and here’s the calculation I will recommend.

Create a calculation called “Column Number” as shown below:

CASE MONTH([Date]) WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 1
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 1
WHEN 11 THEN 2
WHEN 12 THEN 3
END

This was the ONLY difference between Ben and Sean’s solutions.

Here was Ben’s calculation:

IF DATEPART('month',[Date]) % 3 = 0 THEN 3
ELSEIF DATEPART('month',[Date]) = 2 OR DATEPART('month',[Date]) = 5 OR DATEPART('month',[Date]) = 8 OR DATEPART('month',[Date]) = 11 THEN 2
ELSE 1
END

There are likely several more ways to create the necessary logic, so at the end it may come down to performance. On this data set, it really doesn’t matter to me if it finishes in .02 seconds or .03; the difference isn’t noticeable. But if I’m working with larger data, I want to design my workbooks as efficiently as possible. Personally, I find CASE statements to perform slightly better than IF-THEN.

Secondly, I like simplicity and the CASE statement is simple if nothing else.

AND, I love the customer first and foremost! So in this example, I will use Sean’s CASE statement to arrange our columns. Getting back to our regularly-scheduled program:

  1. Drag “Column Number” from Measures to Dimensions
  2. Drag “Column Number” to the left of “WEEKDAY(Date)” in the Columns shelf
  3. Set view to "Fit to Height"


Step 6: Add a Level of Detail expression

Almost there. Just need to compress this accordion together if you will. Rather than using WEEK(Date), we need to create an index that will set the week number = 0-5 at the month level. Since we’re not aggregating our visualization at the month level, a Level of Detail expression works great here:

  • Create a calculation called “Week Index” as follows:
  • DATEPART('week', [Date]) - {FIXED DATEPART('month',[Date]): MIN(DATEPART('week', [Date]))}
    This calculation first looks at the week number at the level of the visualization and then subtracts the minimum week number at the Month Level.

  • Drag “Week Index” from Measures to Dimensions
  • Drag “ Week Index” on top of “WEEK(Day)” in the Rows shelf


    Step 7: Break it down by quarters

    Now we need to break the visualization four times to get each quarter. Easy enough.

    1. Right-click and drag “Date” to the left of “Week Index” in the Rows shelf and select “Quarter(Date)” from the discrete date parts


    Step 8: Add your final polish

    To polish this up…

    1. Right-click “Week Index” and uncheck “Show Header”
    2. Right-click “Column Number” and uncheck “Show Header”
    3. Right-click on “Quarter of Date” and select “Hide Field Labels for Rows”
    4. Hide the title
    5. Right-click on “Q1” and select “Format”
    6. Change the Default Header Alignment to “Middle Center”
    7. Right-click and drag Date to the left of “Column Number” in the Columns shelf and select “YEAR(Date)” from the discrete date parts (last right-click and drag, I promise!)
    8. Right-click in whitespace and select Format
    9. Go to Format Borders and change the pane setting for both the Row and Column Divider to the thickest width line and color set to white
    10. Click the Color shelf and change the borders color to the bottom gray in the second row from the help the individual months pop!


    Step 9: Make it interactive

    To add a little interaction:

    1. Right-click “YEAR(Date)” in the Columns shelf and select “Show Filter”
    2. Change filter type to “Single Value (List)” for a radio selector (remove any previous date filters)


    I’m personally a big fan of these types of calendars. I’ve been lucky enough to come across several use cases in my endeavors and hope you will, too.

    And as for my additional challenge! I’ve still yet to come across a really good, scalable solution for adding in the month names. If you have a way, please do share. Or if you have a better way to create a 12 month calendar, again, please share.

    Comments

    Hi Kevin,

    Fantastic job. This tutorial was super helpful. Big thanks to Andy, Ben, and Sean as well.

    In response to your challenge, I was able to come up with a Month labeling system. In my mind, the challenge was possible using a trick similar to using a dual axis chart to overlay a separate label. I ended up making that happen, but only by creating a LOD expression to mimic the level of detail on your view, that also returned the first of each Month. This let me create two labels, one for the first day of the month, and one for the remaining days.

    It's not perfect, I had to sacrifice the top right formatting. The labels only make visual sense if you center them vertically and horizontally. But I thought it was close enough that I'd share.

    Here's the Tableau Public link: https://goo.gl/ZNxBr1. Please feel free to make any corrections or modifications!

    Thanks again for your great post!

    --Ben

    A quick addition: I realize I massively over-complicated things trying to use the dual axis chart when, in effect, all I needed was a custom label. I've updated the workbook to reflect this. It now uses the LOD calculation to show the month abbreviation and day on the first day of the month and just the day of the month for all other days. Best part about this: saves the top-right formatting the dual axis broke.

    Anyway, it's nothing major to "solve" your challenge, but it's a start. Just standing on the shoulders of giants like yourself.

    --Ben

    Love it Ben! very creative and great use of LODs! I've been at this for a long time and learn something new nearly every day. hardly a giant. the community as a whole is the giant and glad you're embracing it.

    I have been trying to get the full year for a while now. I loveeeee this dashboard. I've been able to do an individual month with no problem before. I have 2 questions and would really appreciate if anyone can help me.

    1- I am using T9.3 on our server so I cannot use T10+; I cannot create the fixed calculation in step 6 on T9.3 DATEPART('MONTH'[DATE])does not work. Is there a work around for this please?

    2- How do I account for any null dates like holidays or if I do this on a product level and the product only gets sold 10 times in a given month, you will only get 10 dates and the viz only shows 10 marks now. I have tried to use an excel sheet with all dates within my range and then blend with my sales data but that too did the same since I cannot do a left outer join.

    *correction: you cannot apply a fixed to a DATEPART in Tableau 9.3.

    I figured out question 1. If anyone can answer question 2 that would be awesome.

    Nasir...please check out this recent post in the community. some brilliant minds collaborating on this one. I believe you'll find your answer in here. The answer will depend on whether you have actual NULLS or if you need to pad your data. thanks for your comments!

    Where can I find this post. Been to forums and searched. datepart.

    https://community.tableau.com/message/224692

    My apologies...i left that little detail out, the URL

    Nice post.

    FYI, the Column Number calculation in step 5 can be simplified to
    (MONTH([Date]) - 1) % 3

    less typing, but same idea

    Nice post.

    FYI, the Column Number calculation in step 5 can be simplified to
    (MONTH([Date]) - 1) % 3

    less typing, but same idea

    Thanks Alex! I prefer your cleaner approach. simplicity is bliss

    thanks for the article folks. how do you display additional data on the calendar?

    you can likely place more info either in the label or the tooltips...my preference would be to use the tooltips to keep the calendar clean and quick to consume

    You beat me to it! Was just about to leave a comment. Great collaboration here!!

    Thanks for your blog and thanks to Andy Kriebel and you for the challenge launched on Workout Wednesday. I could not get the challenge out of my mind. I hope you like the result. -> goo.gl/NI0vfb

    In Step 6 after dropping "Week Index" on top of week, tableau goes back out to database and queries. This just sits there and does not complete. before this step the query would run for 2 seconds only. Even when I limit time frame to 1 month.

    I am on Tableau 10.2 and pulling Data from Oracle. There is data for every day of the year in the pull.

    Thanks so much for the great resource! I'm having problems getting the dateparse to function in 9.2 - any ideas on how to tweak that formula in step 5 to be more backwards compatible?

    Mike...thanks for the comments! 1st...You are missing out on a TON of great features still being on 9.2. My recommendation would be to upgrade soon as these are stable releases. We're on 10.2 now.
    Designing for all previous versions would be a taxing exercise to say the least
    With that said, I'm curious as to what you're unable to accomplish in 9.2 as DATEPARSE was available (mind you, I haven't tried in 9.2 myself)

    Thanks for the reply! unfortunately we haven't yet gotten approval for 10.2 across my area of employment. I agree the new features look fantastic. when I try to run the following:
    DATEPART('week', [Date]) - {FIXED DATEPART('month',[Date]): MIN(DATEPART('week', [Date]))}

    I get an error saying: Only column names are permitted in the dimensionality declaration

    Tableau then identifies the D in datepart immediately after FIXED as the culprit. I actually ran the code in 10.2 with my data to make sure I wasn't doing something else wrong and it worked like a charm - so I just need to tackle this in the 9.2 environment :/

    ANY suggestions appreciated!

    Hi Mike,

    The issue is that previous to Tableau 10, you could not use calculations on dates as the dimension declaration in an LOD. If you create a calculated field that is DATEPART('month',[Date]), then use that as the first part of your LOD, you should be set!

    --Ben

    This worked perfectly! Thank you!!

    There's a mistake in step 4.5

    “WEEKDAY(Date)” is on Columns, not Rows


    Add new comment