
1. Re: Total Hours Per Class & Campus
swaroop.gantela Jul 17, 2018 7:04 PM (in response to Luke Vargo)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([TimesBegin Time],2)="12" AND //midnight
RIGHT([TimesBegin Time],1)="a"
THEN 0
ELSEIF LEFT([TimesBegin Time],2)="12" //noon
THEN 12
ELSEIF RIGHT([TimesBegin Time],1)="a" // am
THEN INT(LEFT([TimesBegin Time],2))
ELSE INT(LEFT([TimesBegin Time],2))+12 // pm add 12
END)
,
INT(MID([TimesBegin 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 campusbuildingroom 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
Please see workbook v10.0 attached in the forum thread.

275983class.twbx 8.6 MB
