Durations by Hour

[cfountain72]

Good Morning,

I am trying to determine the amount of time spent in a certain status for each hour within a time period. For instance, in the example below, the Duration starts at 23:55 and ends at 02:50.

In the load script, how would I generate the Date Hours and associated Duration in each hour (in yellow)? I tried using iter(), but that seems to work only for dates/integers? Below is my attempt along with some dummy data, which yields exactly 0 rows  :^(

LOAD [Work Center Name],

timestamp(floor([Start Date], 1/24)+ IterNo() - 1) as StartHour,

If(timestamp(floor([Start Date], 1/24)) = timestamp(floor([Stop Date], 1/24)), (Frac([Stop Date]) - Frac([Start Date])) * 24,

If(timestamp(floor([Start Date], 1/24)) + IterNo() - 1 = timestamp(floor([Start Date], 1/24)), (1 - Frac([Start Date])) * 24,

If(timestamp(floor([Start Date], 1/24)) + IterNo() = timestamp(ceil([Stop Date], 1/24)), (Frac([Stop Date])) * 24, 24))) as Duration

While timestamp(floor([Start Date], 1/24)) + IterNo() <= timestamp(ceil([Stop Date], 1/24))

;

LOAD * INLINE [

    Work Center Name, Start Date, Stop Date

    AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525

    AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945

    AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253

    AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545

    AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756

    AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324

];

Any suggestions? Thanks, I would appreciate it.

[Antoine04]

Hello,

Do you need to have the details by hour ?

If yes, then I guess you need to create in the script a row for each hour between your 2 dates.

That can be done with a while() function and then probably using the IntervalMatch() formula to get all the hours.

Regards,

Antoine

[cfountain72]

Thanks Antoine04,

I have the Start and Stop Times for each duration. I need to be able to calculate the amount of time per hour that the duration covers (the yelow area in the screenshot).

[Antoine04]

Hello,

I managed to create the row with the formula I told you above.

Then I have this :

Does it fit what you need ?

Regards,

Antoine

[cfountain72]

Thanks! That is helpful, but doesn't quite yield the results I'm trying to get. For instance, in row 1, the Duration should be amount of time from the Start Time (6/9/2020 at 19:34) until 20:00, or ~26 minutes. The second row would be 60 minutes, and on, until the last row, which would be 6/10/2024 at 09:00 until the Stop Time (09:29), or ~29 minutes. Does that make sense?

[Antoine04]

Yes it does. What about that ?

[cfountain72]

Thanks. I appreciate your time on this; getting very close! But the rows should never be more than one hour. So rows 2, 3, 4, 5, etc. would be 01:00:00, until the last row, which would be 00:29:45. Basically, we're cutting the duration into separate 1 hour buckets, as opposed to accumulating them.

[Antoine04]

OK, so here we are 🙂

[cfountain72]

That's great. Thanks! Was anything done in the Load Script, or just the formula you have for that column?

[Antoine04 · Accepted solution]

Attached you will find the qvf

Regards,

Antoine