topic Utilization / Hour in QlikView

Utilization / Hour

montgomery7aust — Sat, 04 May 2013 02:25:48 GMT

Hi Community,

I have a calendar that includes hours, and now I need to measure utilization of repair bays over those hours. For example, in one facility we have three repair bays with repairs ranging from 30 minutes to 5 hours. I want to see how many minutes / hour are used by the 3 bays over 24 hours (i.e. if all three bays are used for that hour then they would receive 180 minutes of use for that hour). The times for the repair bay usage are timestamps for start and end times with no further delineation.

I haven't stumbled upon anything else that seems to explain this so any help is appreciated!

Thanks,

-Austin

Re: Utilization / Hour

swuehl — Sat, 04 May 2013 10:45:26 GMT

This seems quite similar to the problem discussed here.

Hope this helps,

Stefan

Re: Utilization / Hour

montgomery7aust — Sat, 04 May 2013 19:25:18 GMT

That does help a lot, thank you. However, It looks like they are just counting the number of people in a given hour and I'd like to take it one-step further in granularity. If a bay is used for only twenty-three minutes of the hour, and the other two are utilized for the entire hour, how would I generate a utilization of 143 minutes across the three bays for that hour?

I suppose I'm struggling most bucketing my bay utilization into minutes / bay / hour of day.

Re: Utilization / Hour

swuehl — Sun, 05 May 2013 00:03:25 GMT

So then it would be probably better to do it like this (the timestamp DT here describes the interval of the timestamp till timestamp + 1h):

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/9/12 12:20 PM,8/10/12 01:13 AM

8/9/12 09:20 PM,8/10/12 12:13 AM

];

TMP:

LOAD ID,

timestamp#(timestamp((floor(DT1*24)+iterno()-1)/24)) as DT,

if(iterno()=1, DT1, timestamp#(timestamp((floor(DT1*24)+iterno()-1)/24))) as Start,

if(floor(DT1*24)+iterno()-1=floor(DT2*24), DT2, timestamp#(timestamp((floor(DT1*24)+iterno())/24))) as End

Resident INPUT

while floor(DT2*24) >= floor(DT1*24)+iterno()-1;

left join (INPUT)

LOAD

ID,

DT,

interval((End-Start)) as Duration

Resident TMP;

drop table TMP;