Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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. 

5 Replies
Master II
Master II

can u upload your excel/raw data?

Not applicable

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.

Master II
Master II

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

Not applicable

Thank you so much Frank, that worked!

Master II
Master II

then please close this thread by marking the correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful