6 Replies Latest reply on May 13, 2016 6:59 PM by swaroop.gantela

    How to put the same entity into multiple category

    Davy Lam

      Hi:

          I have a data set that shows the time a classroom is being used. The data set has two columns: Start Time (counting in minutes, e.g. 480 means 8:00 am) and End Time (counting in minutes) as shown below. I need to calculate the classroom utilization in each time slot (e.g. from 8:00 am to 9:00 am)

       

      Start_Time        End_Time

        

      423479
      479480
      481485
      480540
      490500
      490539
      539540
      539541
      540542

       

      I want Tableau to show the following viz. If a class starts at 539 and ends at 541, the class is across two time slots. I am unable to make the viz to show a class to appear in two time slots as shown below. Any suggestion is welcome.

       

         

      4814858:00 am to 9:00 am
      4805408:00 am to 9:00 am
      4905008:00 am to 9:00 am
      4905398:00 am to 9:00 am
      5395408:00 am to 9:00 am
      5395418:00 am to 9:00 am
      5395419:00 am to 10 am
      54054210 am to 11 am
        • 1. Re: How to put the same entity into multiple category
          swaroop.gantela

          Davy,

           

          I'm not sure, but I think it will by tricky to add a second row.

          Would it be sufficient add a second column?

          204024time.png

          • 2. Re: How to put the same entity into multiple category
            Davy Lam

            Adding another column will not work. Eventually, I need the viz to show all the hours all the classrooms are occupied for each time slot on each day of the week as shown below.

             

            • 3. Re: How to put the same entity into multiple category
              swaroop.gantela

              Davy,

               

              Here's another attempt using v9.3's Union capability.

              I unioned the set with itself to get two copies of each entry.

              The ClassHour calculated field only returns the second hour if needed:

               

              IF CONTAINS([Table Name],"txt1")

              THEN STR([StartHour])+":00 to "+STR([StartHour]+1)+":00"

              ELSEIF [StartHour]<>[EndHour] AND [EndMin]<>0

              THEN STR([StartHour]+1)+":00 to "+STR([StartHour]+2)+":00"

              END

               

              Not completely sure if this will play well with your utilization calculations,

              but maybe it can be a start.

              204024timeB.png

              • 4. Re: How to put the same entity into multiple category
                Davy Lam

                Hi Shawn:

                   You are right. I have not included all the results. The tricky part is the class starts at 539 and ends at 541. One minute of this class is in the 8:00 am to 8:59 am time slot (i.e. 540 - 539) and two minutes of this class is in the 9:00 am to 9:59 am time slot. So I need to somehow allocate 1 minute to the 8:00 am to 8:59 am time slot and 2 minutes to the 9:00 am to 9:59 am time slot for this one class.  

                • 5. Re: How to put the same entity into multiple category
                  Davy Lam

                  HI Swaroop.Gantela:

                       Your idea of adding more columns may work. I can create multiple Calculated fields like "8 am to 8:59 am" to store the number of minutes that fall in this time slot. Then I have another Calculated field "Time Slot" to track which time slot it is falling into. For example, if a class meeting time is from 8:00 am to 10:20 am. The Calculated field values will be as follows.

                   

                  Calculated Field name       Calculated Field Value       

                  "8 am to 8:59 am"              60 (in minutes)

                  "Time Slot"                         8 am to 8:59 am

                  "9 am to 9:59 am"              60

                  "Time Slot"                         9 am to 9:59 am

                  "10 am to 10:59 am"          20

                  "Time Slot"                         10 am to 10:59 am

                   

                  I sum all the duration in Calculated Field like "8 am to 8:59 am", "9 am to 9:59 am", "10 am to 10:59 am", etc. and store the result in another Calculated Field (e.g. Duration). Now, I have the "Time Slot" Calculated Field and the "Duration" Calculated Field. I put the "Time Slot" Calculated Field on Rows and "Duration" Calculated Field on Columns. Then it should show the following.

                   

                  Time Slot                       Duration

                  8 am to 8:59 am            60

                  9 am to 9:59 am            60

                  10 am to 10:59 am        20

                   

                    This is for one class section. If I apply this procedure to all class sections for all classrooms in one semester, I'll have the total number of minutes a classroom is occupied in a semester. I divided this number by the total available minutes. Then I get the overall classroom utilization per time slot in one semester. I have not tried this out yet but it seems like it will work. Will post the worksheet if it works.

                  • 6. Re: How to put the same entity into multiple category
                    swaroop.gantela

                    Davy,

                     

                    Much time has passed, but I came across this other way to set things up.

                    This may no longer be useful for you now, but I thought I'd add this anyway.

                     

                    I used the methods from here:

                    CROSS JOIN with Tableau's join dialog

                     

                    and made three sheets: your datatable, a list of all the possible minutes,

                    and a list of start and end time for the hour blocks.

                    All contain the same key, and they get joined together.

                    206402data.png

                    Sheet1 has your [Start] and [End]

                    Sheet2 has [Minutes]

                    Sheet3 has [Hour Start] and [Hour End]

                     

                    Then you can filter just the minutes that were used by a particular class

                    [Minutes]>=[Start] AND [Minutes]<=[End]

                     

                    and then group by hour blocks:

                    [Minutes]>=[Hour Start] AND [Minutes]<=[Hour End]

                     

                    I didn't make the fractional use calculation, but you can

                    generate it by setting a 1 for each minute used and then adding it up.

                     

                    There remains some trickiness with the start and end times of the classes.

                    The time 9:00 has to be only part of the 9:00-10:00 block

                    so the class that runs from 479 to 480 has to traverse two blocks.

                    204024hour.png