Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Utilization per hour

Hi Team,

I need help on a utilization calculation that I am working on.

I am trying to calculate utilization per hour for an imaging scanner. My issue is calculating utilization per hour over the year and I would like to add a ceiling of 120mins for exams over 2hours. I saw another example but it is not exactly what I am looking for.  I don't know how to start so any help would be great.

Start_time                              End_time                                   Duration                    7am               8am              9am               10am      

1/1/17 7:30am                         1/1/17 8:30am                         60mins                       30mins          30mins

1/1/17 8:45am                         1/1/10 9:30am                         90mins                                              15mins         30mins        

Please let me know if you need more information.

Thanks,

Kurleigh

4 Replies
MVP
MVP

Re: Utilization per hour

Maybe start with something like this, use BucketHour and DurationBucket to create your statistics:

Set TimestampFormat = 'M/D/YY h:mmtt';

INPUT:

LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

Start_time,                              End_time                      

1/1/17 7:30am,                         1/1/17 8:30am                  

1/1/17 8:45am,                         1/1/17 9:30am                  

];

LOAD RecID,

Iterno() as BucketID,

Hour(Start_time+(iterno()-1)*MakeTime(1)) as Bucket_Hour,

Round(

(RangeMin(End_time,Ceil(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1)))

-RangeMax(Start_time,Floor(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1))))

*60*24) as DurationBucket

Resident INPUT

WHILE Start_time+(iterno()-1)*MakeTime(1) <= Ceil(End_time,MakeTime(1));

Not applicable

Re: Utilization per hour

Thanks Stefan,

I'll give it a try and let you know how it works out.

Kurleigh

Not applicable

Re: Utilization per hour

Hi Stephan,

I am finding that my durations are not lining up correctly with the start time end time. Can you point out where this is happening? I loaded a few more time stamps

Set TimestampFormat = 'M/D/YY h:mmtt';

INPUT:

LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

Start_time,                              End_time                      
   

1/1/17 9:45am,                         1/1/17 10:00am                  

1/1/17 10:15am,                        1/1/17 12:30pm

1/1/17 1:45pm,                         1/1/17 2:45pm                  

1/1/17 3:15pm,                         1/1/17 5:00pm

1/1/17 5:41pm,                         1/1/17 6:23pm                  

1/1/17 7:15pm,                         1/1/17 10:30pm

];

LOAD RecID,

Iterno() as BucketID,

Hour(Start_time+(iterno()-1)*MakeTime(1)) as Bucket_Hour,

Round(

(RangeMin(End_time,Ceil(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1)))

-RangeMax(Start_Time,Floor(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1))))

*60*24) as DurationBucket

Resident INPUT

WHILE Start_time+(iterno()-1)*MakeTime(1) <= Ceil(End_time,MakeTime(1));

MVP
MVP

Re: Utilization per hour

After correcting the incorrect field name spelling (Start_Time instead of Start_time), this is what the script returns:

      

RecIDDurationStart_timeEnd_timeBucket_HourBucketIDDurationBucket
1151/1/17 9:45am1/1/17 10:00am9115
21351/1/17 10:15am1/1/17 12:30pm10145
21351/1/17 10:15am1/1/17 12:30pm11260
21351/1/17 10:15am1/1/17 12:30pm12330
3601/1/17 1:45pm1/1/17 2:45pm13115
3601/1/17 1:45pm1/1/17 2:45pm14245
41051/1/17 3:15pm1/1/17 5:00pm15145
41051/1/17 3:15pm1/1/17 5:00pm16260
41051/1/17 3:15pm1/1/17 5:00pm1730
5421/1/17 5:41pm1/1/17 6:23pm17119
5421/1/17 5:41pm1/1/17 6:23pm18223
61951/1/17 7:15pm1/1/17 10:30pm19145
61951/1/17 7:15pm1/1/17 10:30pm20260
61951/1/17 7:15pm1/1/17 10:30pm21360
61951/1/17 7:15pm1/1/17 10:30pm22430

could you point to the issue?

Community Browser