6 Replies Latest reply on May 7, 2010 1:58 PM by Richard Leeke

    Calendar heat map

    . rviard

      I'm trying to create a calendar heat map that shows how a group of professionals are spending their times. They all have consultations with groups of students (1, 2, 3 or more), or do other activities like prep work etc. (null value for group size).

       

      My data includes professional's name, day of week, start time, end time, duration (calculated), group size and a few other things.

       

      I created a chart using continuous time as the vertical axis, and Day of week as the horizontal axis and used squares to represent the session times. I assigned sum of group size to color and use a gradient. If the value is null, i assigned the color gray.

       

      It almost works (and looks great) except that the time block do not lineup correctly with the time scale. I was hoping that the square would work in such a way that the top of the square starts at the start time, then the next square would overlap to start at the next time, etc. One thing to note is that the sessions are never the same, some are 30mn, some 45 or 15mn, different each day, etc.

       

      I wonder if I should use bars instead where the base of the bar is the end time, and the session time is the size of the bar.

       

      Has anyone been successful creating a calendar with Tableau?

       

      Thanks.

        • 1. Re: Calendar heat map
          guest contributor

          Calendars are a frequent chart type that we see folks creating off of standard heat maps.  It's simply having the right date aggregations to arrange it to a calendar look and formatting the cells to look more like a Calendar.  One of the blog article includes a walk through on how to create this:

           

          http://www.tableausoftware.com/blog/calendar-data-visualization

           

          I actually used bars instead because I was using it to track of my activity like you.  I would keep things relatively simple on your shelf such that

           

          Columns: [Month] [Weekday]

          Rows:[Week Number] [Duration] (Depending on how much this varies, [%of Total Duration] can be used instead of [Duration] since it will allow you to fix you axis)

           

          Color: [Group Size]

           

          It depends on the view but it might be worth consider either applying [Professionals] as a text label or a filter for your data.  I would steer clear of using Gantt bars as horizontal bars don't make good calendar views.

          • 2. Re: Calendar heat map
            . rviard

            Thanks, this is useful, but not exactly what I was looking for. I'm only interested in showing a typical week's worth of data (it's the same schedule repeating every week), so it's a weekly view with days at top and hours on the left. One issue is that the time slots are not all the same length. For example, monday may be 8:45 - 9:00, 9:00 - 9:30, 9:30, 10:15, etc. and different for each day of the week. So the calendar needs to show different size boxes for each time slot.

             

            By some chance, it almost looks like that, because the squares overlap each other, but it seems that the square is centered on the start time instead of top aligned.

             

            As for the professionals, I've used them as an additional left axis, so each individual calendar is grouped by professional. That part works well.

             

            I've been trying all sorts of things, but maybe it just can't be done. Too bad because that would have been a very useful visualization for my project. Now it looks like I'm going to have to create this by hand in Excel or something. Ugh!

            • 3. Re: Calendar heat map
              James Baker

              Well rviard, you could go with bars, or you could use a calculated field of "start + (end - start)/2" so the center of the box being the middle of your time period would work out right?

              • 4. Re: Calendar heat map
                . rviard

                I actually have a duration field which was a field I had in excel, so I tried:

                [Start time] + ([Duration]/2)

                 

                Except I get an error: "Can't divide a date time by a integer"

                 

                I also tried the end - start and got the same result. Any idea how I can convert the date? I didn't find any function that looked like it could do it.

                 

                Sorry for all the newbie questions...

                • 5. Re: Calendar heat map
                  James Baker

                  A duration shouldn't be a date.  A duration should be a number (of seconds, hours, whatever).  Right click on that field in the Data pane, select "Describe", then "Load" the domain to see what what's in there.

                   

                  For end - start, you'll need to use the DATEDIFF. For adding a number to a date, you'll need to use DATEADD.

                  • 6. Re: Calendar heat map
                    Richard Leeke

                    And for the duration to give you the length of your gantt bars correctly you'll need it to be expressed in (fractional) days so 12 hours = 0.5, etc.