Splitting a Duration into amounts by hour

[cfountain72]

Good Afternoon,

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 failed 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

];

Thanks for any help you can provide.

ps. I asked this question earlier, but I don't have access to Qlik Sense Desktop, so I couldn't open the proposed solution. There are several variants floating around, but they don't allow for situations where the durations cross multiple dates.

[rubenmarin · Accepted solution]

Hi, the unit of measure is day, so to add or substract hours you need to do 1/24, try with:

LOAD [Work Center Name], [Start Date], [Stop Date],
	timestamp(floor([Start Date], 1/24)+ IterNo()/24 - 1/24) as StartHour,
	If(floor([Start Date], 1/24) = floor([Stop Date], 1/24)
	  ,(Frac([Stop Date]) - Frac([Start Date]))
	  ,If(floor([Start Date], 1/24) + IterNo()/24 - 1/24 = floor([Start Date], 1/24)
	    ,frac(Ceil([Start Date], 1/24) - Frac([Start Date]))
	    ,If(floor([Start Date], 1/24) + IterNo()/24 = ceil([Stop Date], 1/24)
	      , Frac([Stop Date]) - frac(floor([Start Date], 1/24)+ IterNo()/24 - 1/24)
	      , 1/24))) as Duration
While timestamp(floor([Start Date], 1/24)) + IterNo()/24 <= 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
];

[cfountain72]

Thanks for the incredible help! Hope you have a great weekend.

[cfountain72]

Hi Ruben,

Thanks again for your help. Not sure how often you use that code, but I came across an example where it seems to drop off the last hour of activity. The interval below ends at 10:38, but the data ends at 10:00, excluding the last 38 minutes of the duration. Can you please run this and see if you get the same result?

LOAD [Work Center Name], [Start Date], [Stop Date],

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

If(floor([Start Date], 1/24) = floor([Stop Date], 1/24)

  ,(Frac([Stop Date]) - Frac([Start Date]))

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

    ,frac(Ceil([Start Date], 1/24) - Frac([Start Date]))

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

      , Frac([Stop Date]) - frac(floor([Start Date], 1/24)+ IterNo()/24 - 1/24)

      , 1/24))) as Duration

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

;

LOAD * INLINE [

    Work Center Name, Start Date, Stop Date

     AA, 2024-02-03 15:29:00.000, 2024-02-08 10:38:00.000

     ];

Thanks again,

Chris

[rubenmarin · Accepted solution]

Hi, this is not a code I use, I just modified your code.

For this case it works with this modification:

LOAD [Work Center Name], [Start Date], [Stop Date],
timestamp(floor([Start Date], 1/24)+ IterNo()/24 - 1/24) as StartHour,
If(floor([Start Date], 1/24) = floor([Stop Date], 1/24)
,(Frac([Stop Date]) - Frac([Start Date]))
,If(floor([Start Date], 1/24) + IterNo()/24 - 1/24 = floor([Start Date], 1/24)
,frac(Ceil([Start Date], 1/24) - Frac([Start Date]))
,If(floor([Start Date] + IterNo()/24, 1/24) = ceil([Stop Date], 1/24)
, Frac([Stop Date]) - frac(floor([Start Date], 1/24)+ IterNo()/24 - 1/24)
, 1/24))) as Duration
While timestamp(floor([Start Date] + IterNo()/24, 1/24)) <= timestamp(ceil([Stop Date], 1/24))

[cfountain72]

Awesome. Thanks...that modifcation works like charm! Have a great week. If you ever come to Tampa, I owe you a drink.