12 Replies Latest reply on Sep 13, 2019 10:14 AM by Salma Mirza

    Space Utilization Chart

    Salma Mirza

      Hi all

      I am stuck with a date/time problem. I am trying to develop a room utilization dashboard. My data is based on a school with multiple rooms in multiple buildings. The courses are also listed. What I want to do is to show class start time and make a kind of Gantt bar chart that would show the start time and the length of class. The length of the bar should show the length of class like shown below (I have had some success in showing the chart).

       

      My problem is that Tableau appears to be truncating the minutes part of the start time. there are sessions that begin at the top of the hour but others that are starting at say 8:30 am or 10:15 am. That is not showing in the visual. They all seem to be rounded off to start at the top of the hour (which is incorrect). My Tableau sample workbook is attached. Please help!

        • 1. Re: Space Utilization Chart
          Branden Kornell

          Have you tried doing a (literal) Gantt chart? It's one of Tableau's chart types that's available in Show Me.

           

          Build a Gantt Chart - Tableau

          • 2. Re: Space Utilization Chart
            Branden Kornell

            Here's what the Gantt chart looks like. The formula for Mtg Length is: DATEDIFF('minute', [Mtg Start], [Mtg End]) / (60*24)

             

            I see that you're trying to aggregate across days. You can do this, but the marks might overlap in confusing ways; you'll need to give it some thought.

             

            1 of 1 people found this helpful
            • 3. Re: Space Utilization Chart
              Salma Mirza

              Thank you Branden. This was really helpful. I have two followup questions:

              1. It worked but i'm unable to understand the logic behind it. I thought the formula DATEDIFF('minute', [Mtg Start], [Mtg End]) should be enough to convert the time difference into minutes. Why did we need to divide it by (24*60). Sorry, I haven't worked a lot with date/time conversions.

              2. The hours in the axis match perfectly with time and length of time but the tooltip gives an absurd Mtg Start time which has a date (see below). Is it possible to fix so that I can see only the 9:30 and not the date?

              • 4. Re: Space Utilization Chart
                Branden Kornell

                Luckily, the answers to both of these are pretty straightforward.

                 

                1. It worked but i'm unable to understand the logic behind it. Why did we need to divide it by (24*60)?

                 

                Keep in mind: your axis is a time axis, not whole numbers.

                 

                Similar to Excel, each day has a length of 1. In Excel, you can directly subtract datetimes instead of using DATEDIFF, so Jan 2 - Jan 1 = 1. Hours are fractions of days, so 12 hours is 0.5, 6 hours is 0.25, etc.

                 

                The same thing is happening in Tableau. We're calulating the datediff in minutes, but we have to convert it to a fraction of a day to size it correctly on the axis. So 50 minutes / (60 minutes/hour * 24 hours/day) = 0.0347 days.

                 

                 

                2. The hours in the axis match perfectly with time and length of time but the tooltip gives an absurd Mtg Start time which has a date (see below). Is it possible to fix so that I can see only the 9:30 and not the date?

                 

                Right-click on [Mtg Start] and select Default Properties --> Date Format

                 

                Click Custom, and set the format to h:nn AMPM.

                 

                 

                 

                9-11-2019 8-40-16 AM.jpg

                1 of 1 people found this helpful
                • 5. Re: Space Utilization Chart
                  Salma Mirza

                  Brilliant! Thank you

                  • 6. Re: Space Utilization Chart
                    Salma Mirza

                    Branden

                    I have an additional, slightly more complicated question on this. On my datasheet, I have both a start date and an end date, specifying, when in the term a course is supposed to begin and finish. Most courses start on one date (the beginning of the term), however, several others have different start and end dates. I want to in my Gantt chart when a course is occupying space and when its over. So I used the Page feature that animates the visual based on start and end times:

                    But the problem is that the fading option only works when the start-end date combination is in a previous Page. i want the end date to signify when the bar should fade or completely vanish. Basically I want course schedulers to be able to see empty spots between a given set of days. Does that make sense? I'm still trying to wrap my head around this but if I can have a breakthrough on this, it'll be wonderful.

                    Thanks in advance

                    • 7. Re: Space Utilization Chart
                      Branden Kornell

                      Can a post a copy of your most recent workbook?

                      • 8. Re: Space Utilization Chart
                        Salma Mirza

                        Sorry, I thought I did. here it is attached

                        • 9. Re: Space Utilization Chart
                          Branden Kornell

                          Sorry; I was looking at the data preview, which didn't show your attachment. You did it correctly.

                           

                          I understand what you're trying to do, but you won't be able to accomplish it using the Pages shelf with both [Start Date] and [End Date].

                           

                          Instead, you need a single date field that represents the week or individual day. You'd need to create a data scaffold that would duplicate the rows and make an entry for each class on each day or week of interest.

                           

                          Ken Flerlage has as a good blog on data scaffolds here: https://www.kenflerlage.com/2019/03/date-scaffold.html

                           

                          As a simplified example, take this row of data:

                                

                          CourseDay ColumnMtg EndMtg StartStart DateEnd Date
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/2016

                           

                          You'll need to create a scaffold that includes each week in which this class occurs. (It may be easier to show each individual meeting, and then calculate the week.)

                           

                                 

                          CourseDay ColumnMtg EndMtg StartStart DateEnd DateWeek
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20161/11/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20161/18/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20161/25/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20162/1/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20162/8/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20162/15/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20162/22/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20162/29/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20163/7/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20163/14/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20163/21/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20163/28/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20164/4/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20164/11/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20164/18/2016
                          APSM 2442Wednesday12/30/1899 11:50:00 AM12/30/1899 9:00:00 AM1/15/20165/2/20164/25/2016

                           

                          Yes; this will increase the size of your data. But in your example, you only have 9,000 rows. If you duplicate this to have a row for all 13 weeks of the semester, it's only 117,000 rows, which is no problem for Tableau to handle. Even a few million rows will be fine once it's converted to a .hyper file.

                          • 10. Re: Space Utilization Chart
                            Salma Mirza

                            This is brilliant! again! My only problem is that I would either have to get IT to change the query used to pull the data so that it adds the meeting week rows (most courses are meeting multiple times a week, so its going to be 9,000 times 13 times 3 ).  The other option is that I do it in Excel - which looks like a formidable task.  Is there a way I can add the rows in Tableau or Tableau Prep?

                             

                            Thank you so much for your help

                            • 11. Re: Space Utilization Chart
                              Branden Kornell

                              There's actually a really good article on how to do this in Tableau Prep:

                               

                              Scaffold data with Tableau Prep to fill gaps in your data set | Tableau Software

                               

                              You may be also be able to create this directly in Tableau's query window with a custom join.

                               

                              If weekly data is good enough, I'd suggest just doing the 13 weeks (did class X meet during the week?), and putting [Week Ending Date] on the Pages shelf. If you put individual [Class Date] on the pages shelf, your data will disappear/reappear depending on if it's a MWF or T/TH class.

                              • 12. Re: Space Utilization Chart
                                Salma Mirza

                                This is genius.... Thank you so much