3 Replies Latest reply on Mar 23, 2017 2:22 PM by Kaz Shakir

    How to calculate classes that are in session throughout the day.

    Nick Bummara

      I have the course start times and end times. I want to be able to see how many classes are in session at 15 minute intervals.

      I currently have the course times in military format using integers.

      Whats the best way to do this?

       

       

      I created formula that has the course length. (end time - beg time)

       

      Then is created " COUNTD(IF ([Crs Sectn Beg Time] + [Crs Section Length] )<= [Crs Sectn End Time] then 1 END) "

        • 1. Re: How to calculate classes that are in session throughout the day.
          Kaz Shakir

          Nick,

          It's difficult to say without looking at your workbook - if you can post a sample, that would help.

           

          Just off the top of my head, I'm thinking that you might need another table in your data that is similar to a calendar table, but, in this case, would have a row for each individual 15 minute time point.  And then for each of these time points, you can check to see if a class is in session or not during that time.

           

          Kaz.

          • 2. Re: How to calculate classes that are in session throughout the day.
            Nick Bummara

            Kaz,

             

            Thanks for your quick reply!

            Here is a link to the workbook.

             

            Tableau Public

             

             

            The graph shows the number of classes that start at a specific time. I want to know how many are in session at any given point in time.

             

            Nick.

            • 3. Re: How to calculate classes that are in session throughout the day.
              Kaz Shakir

              Nick,

              I don't know if this is going to work for you or not.  It's not a very elegant solution.  What I did was create two new tables: (1) the first is the time table, that has a single row for each 15-minute-increment time point, and (2) the second is a table that contains a single row for each class and each time point.  This second row contains a little over 41,000 rows.   Then joining these tables along with your data table, I can create a new calculated field called "IsClassInSessionAtThisTime", that looks like this:

               

              And that measure can then be summed at each time point to determine the number of classes in session at any given time point.

               

              I'm attaching a sample workbook for you to examine.  Like I said, this is not a very elegant solution, but it might get to what you are trying to do.

               

              Hope it's helpful.  Please let us know if thsi meets your needs.

              Kaz.

               

              p.s., I just noticed that I did the 15-minute increments incorrectly - sorry about that.  The concept is still the same, so I hope you can get the idea from this example, and then implement it correctly if it's what you need.