topic Re: Equipment Utilization by Hour in QlikView

Equipment Utilization by Hour

Tue, 01 Dec 2015 17:11:33 GMT

Hi,

I am looking to determine the utilization of equipment by hour. I have created hour buckets and can use IntervalMatch to get the sum of the SecondsUsed based on start time, but what do I do if the SecondsUsed runs over into the next bucket? Are buckets the wrong approach for this? My main goal is to determine the busiest periods based on utilization. A bonus would be to determine how often all 4 machines are in use...

Take, for example, the first row from my data below:

StartTimestamp: 2015-11-01 00:59:51.000

SecondsUsed: 24

ItemID: 2

Therefore the usage for that one record should be split between two buckets:

Bucket: 1

StartTime: 2015-11-01 00:00:00

EndTime: 2015-11-01 00:59:59

Usage: 9 seconds

Bucket: 2

StartTime: 2015-11-01 01:00:00

EndTime: 2015-11-01 01:59:59

Usage: 15 seconds

Here is an example of the raw utilization data that I have:

Fact:

LOAD * INLINE [

StartTimestamp, SecondsUsed, ItemID

2015-11-01 00:59:51.000, 24, 2

2015-11-01 00:47:40.000, 24, 3

2015-11-01 00:49:49.000, 30, 1

2015-11-01 12:50:19.000, 31, 0

2015-11-02 00:51:12.000, 30, 3

2015-11-02 12:51:53.000, 30, 1

2015-11-02 16:06:32.000, 28, 2

];

Please let me know if any more details would be helpful.

Thanks in advance for any and all help.

Re: Equipment Utilization by Hour

sunny_talwar — Tue, 01 Dec 2015 18:18:28 GMT

Not sure how the data is getting divided into two buckets. How did you derive 9 seconds and 15 seconds?

Re: Equipment Utilization by Hour

Tue, 01 Dec 2015 18:32:05 GMT

Sorry Sunny,

I did make a mistake in my buckets which may have led to confusion. I have fixed the time frame for bucket 2 above.

Here's why the seconds need to be split into 2 buckets:

Item 2 was put put in use on 2015-11-01 at 00:59:51 and used for 24 seconds, therefore ending in the next hour at 01:00:15.

My goal is to determine how much of the possible time per hour Item 1 was used. I can't put the entire 24 seconds in Bucket 1, as the item was only in use for 9 seconds in that hour. I can't put the entire 24 seconds into Bucket 2 as it was only in use for 15 seconds of that hour. So I need to put the first 9 seconds in Bucket 1 and the remainder into Bucket 2.

Thanks for your help!

Re: Equipment Utilization by Hour

Anonymous — Tue, 01 Dec 2015 18:39:47 GMT

IntervalMatch() and time buckets would be the way I would go.

I have done something similar for comparing staff rostered hours vs. staff actual hours.

Each staff member has various dimensions such as Department and Location so I can select and slice 'n dice with them. I can show count of staff rostered [or actually on site] per time bucket - in my case 15 minute buckets and display on a Line Chart.

Sounds like you need your [Machine ID] as a dimension.

Re: Equipment Utilization by Hour

Tue, 01 Dec 2015 21:09:33 GMT

Thanks Bill. The [ItemID] field in my data is the machine's id.

I agree that buckets and an IntervalMatch would work if everything fit nicely into one bucket or the other, but my question is what do I do with those records that span multiple buckets? It seems like a simple problem, but it's got me stumped.

Thanks again for the help.

Re: Equipment Utilization by Hour

sunny_talwar — Tue, 01 Dec 2015 21:53:11 GMT

May be this:

Fact:

LOAD StartTimestamp,

SecondsUsed,

ItemID,

Interval(If(StartEndHour - StartTimestamp >= SecondsUsed/(24*60*60), SecondsUsed/(24*60*60), StartEndHour - StartTimestamp), 's') as Bucket1,

Interval(If(EndTimestamp - StartEndHour <= 0, 0, EndTimestamp - StartEndHour), 's') as Bucket2;

LOAD *,

TimeStamp(StartTimestamp + SecondsUsed/(24*60*60)) as EndTimestamp,

TimeStamp(Ceil(StartTimestamp, (1/(24*60)))) as StartEndHour;

LOAD * INLINE [

StartTimestamp, SecondsUsed, ItemID

2015-11-01 00:59:51.000, 24, 2

2015-11-01 00:47:40.000, 24, 3

2015-11-01 00:49:49.000, 30, 1

2015-11-01 12:50:19.000, 31, 0

2015-11-02 00:51:12.000, 30, 3

2015-11-02 12:51:53.000, 30, 1

2015-11-02 16:06:32.000, 28, 2

];

Re: Equipment Utilization by Hour

ElizaF — Wed, 02 Dec 2015 12:06:27 GMT

Hi,

One solution is to create a Master Time Calendar and with IntervalMatch function to count the number of seconds of the equipment's utilization on hour.

More information about Master Time Calendar you can find below blog:

The Master Time Table

You can find in the attached file my approach for this.

Hope this helps.

P.S. Reload the file. I removed all the values because of the file's size.

Re: Equipment Utilization by Hour

Wed, 02 Dec 2015 19:46:40 GMT

Thanks Sunny!

This was very promising, until I found out that some tasks span more than 2 buckets. This was not reflected in the test data i was given and presented here, so not a reflection on your solution at all. I think I would need to build a large number of Buckets with conditional statements like this:

Interval(If(EndTimestamp - StartEndHour <= 0, 0, if(EndTimestamp - StartEndHour > 3600, 3600, EndTimestamp - StartEndHour), 's') as Bucket2,

Interval(If(EndTimestamp - StartEndHour <= 0, 0, if(EndTimestamp - StartEndHour > 7200, 3600, EndTimestamp - StartEndHour-3600), 's') as Bucket3,

and so on...

I appreciate the help, but I think I need to find a way to handle N buckets.

Re: Equipment Utilization by Hour

sunny_talwar — Wed, 02 Dec 2015 20:03:28 GMT

That's understandable and I agree that you will have to decide how you want the buckets to be created. We can guide you only to the extent of what is provided to us

But I hope you have a way to move forward from here

Re: Equipment Utilization by Hour

Wed, 02 Dec 2015 21:28:32 GMT

Jonathan, please check the below script. I am simply creating new row if End Time stamp is in different hour.

// More Buckests purpose I took Secs as Mins

TempFactData:

LOAD

Timestamp(StartTimestamp + (MinsUsed/(24*60))) AS EndTimeStamp

;

LOAD * INLINE [

StartTimestamp, MinsUsed, ItemID

2015-11-01 00:59:51, 24, 2

2015-11-01 00:47:40, 24, 3

2015-11-01 00:49:49, 30, 1

2015-11-01 12:50:19, 181, 0

2015-11-02 00:51:12, 30, 3

2015-11-02 12:51:53, 30, 1

2015-11-02 16:06:32, 28, 2

];

FactData:

LOAD

ItemID,

// /*

StartTimestamp,

EndTimeStamp,

MinsUsed,

CounterFlag,

// */

DateHour,

IF(CounterFlag=1, ceil(((DateHour+(1/24))-StartTimestamp)*24*60*60),

IF(Hour(DateHour)=Hour(EndTimeStamp) AND Floor(EndTimeStamp)=Floor(DateHour) , ceil((EndTimeStamp-DateHour)*24*60*60) ,3600)

) AS SecsUsedPerHr

;

LOAD

ItemID,

StartTimestamp,

EndTimeStamp,

MinsUsed,

IterNo() AS CounterFlag,

Floor(StartTimestamp) + ((Hour(StartTimestamp)+IterNo()-1)/24) AS DateHour

Resident TempFactData

While IterNo() <= ((Floor(EndTimeStamp)-Floor(StartTimestamp))*24) + Hour(EndTimeStamp) - Hour(StartTimestamp) + 1

;

DROP Table TempFactData;

Re: Equipment Utilization by Hour

ElizaF — Wed, 02 Dec 2015 22:15:02 GMT

Hi Jonathan,

You don't need N buckets. You need only 24 bucket for 24 hours.

So, can be solved if is created one master time table (with 1 second interval) and linked with the fact table, in order to count the number of seconds per hour / equipment, based on start time and SecondsUsed. See file attached.

Final result will be as in the table below. I think this is what you need.

Re: Equipment Utilization by Hour

Fri, 04 Dec 2015 16:01:37 GMT

Thank you very much for this code. Exactly what I was hoping to achieve!

And thanks to everyone for helping out. This was my first post on the Community and I have to say it rocks. Keep up the good work.