1 Reply Latest reply on Jul 17, 2018 7:04 PM by swaroop.gantela

# Total Hours Per Class & Campus

Hello everyone,

I'm using a workbook that finds the total number of hours that each class runs for based on campus, campus/building/room, day, time, and class section. I've been successful as far as getting the total number of hours for each class (See Sheet 2) but can't seem to figure out how to break out the total number of hours based only on campus/building/room (See Sheet 7). It looks like each it is combining the data for classes that have identical begin and end times instead of just finding the total for campus,building, & room which is what I need.

I am still new to Tableau and am aware that the method I've used to find the total number of hours scheduled on Sheet 2 seems like it may not be the best way to do that, but I guess it worked for what I need.

Any suggestions on this would be greatly appreciated.

• ###### 1. Re: Total Hours Per Class & Campus

Luke,

Welcome to the Forum.

Your calculations look good, but there may be room for some potential condensation.

I made an attempt the get the times straight from the Main sheet without the helper begin/end sheets.

It's just a lot of string manipulation and conversion to military time, as you did in your helper sheets.

The begin time for calc became:

MAKETIME(

(IF LEFT([Times-Begin Time],2)="12" AND   //midnight

RIGHT([Times-Begin Time],1)="a"

THEN 0

ELSEIF LEFT([Times-Begin Time],2)="12"   //noon

THEN 12

ELSEIF RIGHT([Times-Begin Time],1)="a"   // am

THEN INT(LEFT([Times-Begin Time],2))

ELSE INT(LEFT([Times-Begin Time],2))+12   // pm add 12

END)

,

INT(MID([Times-Begin Time],4,2)),  //minutes

0)  // seconds

The same for End time.

The total calc time became:

DATEDIFF('minute',[MakeTimeBegin],[MakeTimeEnd])/60

I likely didn't get the aggregations right, but hopefully the below can give you

the form, or give you ideas.

I then used Level of Detail calculations to do the aggregations, like this one by class:

{ FIXED [Class Title Long],[Time]:MIN([TotalTime])}

// Please note, this may have been a false assumption on my part

// but it assumes that if multiple sections are meeting at the same time and

// in the same room, then I only counted the hours once.

// If each are to be counted separately, change MIN to SUM

Then the Total for campus-building-room would be:

{ FIXED [Campus, Building, & Room],[Enrollment Total Color]:SUM([Total Time by Class,Day])}

Please note that I added all the filters to context, which is needed for the LOD

calculations to take into account the filter selections:

Improve View Performance with Context Filters