How to calculate hours utilized (in percentage) for a facility

[#1]

Hi,

I have a list of conference rooms in a building, and I need to calculate the percentage of hours each room was utilized for a given month relative to the total available hours for that room.

So for room X in Jan '17, total available hours was 200hrs (20 business days x 10hrs availability p/d). In the data source file I have a column for Meeting Start and one for Meeting End time. I've calculated that room X was used for a total of 80hrs. So the room utilization percentage should be .40.

I'm stuck on how to calculate this utilization number in a table or a chart (while counting the business days for each month minus any federal holidays).

If anyone has a suggestion, I would really appreciate it. Thank you. 

[Frank_Hartmann]

can u upload your excel/raw data?

[#3]

Hi Frank,

Unfortunately, my file is too large to be attached here. I have put a small sample file in each tab in the attachment. All the files I'm using are tied together through the unique Booking IDs.

I really appreciate you looking into this. Thank you.

[Frank_Hartmann]

Script:

LOAD Building, 
     [Booking ID], 
     date([Booking Date],'DD.MM.YYYY') as [Booking Date],
     monthstart(date([Booking Date],'DD.MM.YYYY')) as Monthstart,
     monthend(date([Booking Date],'DD.MM.YYYY')) as Monthend,
     NetWorkDays(monthstart(date([Booking Date],'DD.MM.YYYY')),monthend(date([Booking Date],'DD.MM.YYYY'))) as BusinessDays,
     Time([Event Start]) as start,
     Time([Event End]) as end,
     Hour(Time([Event End])- Time([Event Start])) as bookingTime

FROM

(ooxml, embedded labels, table is Sheet1);

create a Straight Table:

5 Dimensions:

Building,

month([Booking Date]),

Monthstart,

Monthend,

BusinessDays

3 Expressions:

sum(bookingTime)

BusinessDays*10

sum(bookingTime)/BusinessDays*10

hope this helps

[#5]

Thank you so much Frank, that worked!

[Frank_Hartmann]

then please close this thread by marking the correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful