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.

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.

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.

Kaz,

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.

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.