topic Re: Calculating duration between two times over different dates- where timestamp is inaccurate in QlikView

Calculating duration between two times over different dates- where timestamp is inaccurate

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

My source data Excel sheet maintains the [Booking Date], [Start Time], and [End Time] in different fields. However, The [Start Time] and [End Time] fields contain a default time stamp date of 1/1/1900. As such, if I have a booking which starts on one date and ends on the next, both Qlikview and Excel treat the data as if the [Start Time] is later than the [End Time], and calculating duration between the two doesn't produce very good results. In addition, I do not have an end date for the booking, from which to create a new timestamp.

example:

[Booking Date] 11/29/2016

[Start Time] 1/1/1900 11:00:00 PM

[End Time] 1/1/1900 9:00:00 AM (but actually 9am the next morning. there is no field which shows that the end date is on 11/30/2016)

I have tried to eliminate the default date so I just have a time to work with:

Time(Frac([Start Time]),'hh:mm') as [Start] (and also for [End Time])

and adding a preceding load, for a result of:

Load *,

interval([End]-[Start],'hh:mm') as [Duration];

Load [Booking ID],

[Booking Date],

Time(Frac([Start Time],'hh:mm')as [Start],

Time(Frac([End Time],'hh:mm') as [End]

From (source doc.xls)

If I format the [Duration] field as "Time", I get the results I am looking for VISUALLY, but it will not calculate correctly. In the example above, the Duration shows as 10:00 when set to Time (with seconds removed) but as -14:00 as interval. If I create a chart which calculates Sum([Duration]) (for all entries over a month, but in this case filtered to the single entry) I get an interval display of 0:00 and a time display of 12:00. Either way, I cannot get the sum to equal 10 hours. If I have two of them, the sum doesn't equal 20 hours.

I have also tried changing my chart format to:

if([Duration]>0,[Duration],Sum([Duration]+24)) (alternately, I replaced 24 with 86400, thinking it was looking for seconds)

This got me nowhere, as I expect I have the syntax all wrong to make this work. I am not sure this is the right direction, anyway.

Could you tell me how I could create a duration field which can correctly calculate hours over two dates, when the timestamp does not provide accurate dates? The final result I am looking for is a chart which calculates the sum total of all durations (ideally, in minutes only) in a given month.

It may be worth noting that I would prefer to load my [Booking Date] as Year([Booking Date]) as Month, Month([Booking Date]) as Month, etc., rather than as a single field. I simplified it for my example and my testing.

Re: Calculating duration between two times over different dates- where timestamp is inaccurate

jason_nicholas — Wed, 30 Nov 2016 19:46:10 GMT

I believe I have solved it. I would appreciate any error correction this board can provide.

Load *,

if([End]>[Start],[End]-[Start],[End]-[Start]+1) as Duration

Load [Booking ID],

Year[Booking Date] as Year,

Month[Booking Date] as Month,

Day[Booking Date] as Day,

Time(Frac([Start Time],'ss')as [Start],

Time(Frac([End Time],'ss') as [End]

From (source doc.xls)

By changing all of the time to seconds, it was much easier to see how the calculations were done. That is how I figured out it was '1' that I needed to indicate a 24 hour period. by subtracting an (apparent) later time from an (apparent) earlier time, I get a negative number. Adding a day to that gives me the appropriate difference between the actual times.

From here, I set my Field displays back to interval 'hh:mm' so that the Start and End time read like a 24 hour clock and the Duration field reads as hours and minutes.

A straight table chart with Month as a dimension and sum(duration) as the calculated dimension gives me a layout where each month is followed by the total of all durations within that month. I set the Duration display to Interval 'mm' and I have a total number of minutes across all Booking IDs in a given month.

On a given month, I might have 300 bookings with hundreds of hours, so it has been difficult to error check. I have grabbed subsets of data and it appears to be working, but if someone identifies an obvious error, I would be eternally grateful.

Re: Calculating duration between two times over different dates- where timestamp is inaccurate

johnw — Wed, 30 Nov 2016 21:18:30 GMT

I could be missing something, but what you've done looks right to me.

Re: Calculating duration between two times over different dates- where timestamp is inaccurate

swuehl — Wed, 30 Nov 2016 21:20:05 GMT

QV internally stores a timestamp as a floating point value, the integer part is coding the date (days since 30 Dec 1899), the fractional part the time (i.e. 0.25 is coding 6 AM, 0.333333 is coding 8 AM, 0.75 --> 6 PM etc.).

One thing to consider maybe:

Could a business case happen where real duration is more than 24 hours?

Re: Calculating duration between two times over different dates- where timestamp is inaccurate

jason_nicholas — Fri, 02 Dec 2016 12:48:48 GMT

I considered that. In my situation, there is no business case for a duration over 24 hours, but I would be interested in a solution which would accommodate that condition.

Re: Calculating duration between two times over different dates- where timestamp is inaccurate

swuehl — Fri, 02 Dec 2016 18:56:36 GMT

That would be hard without somehow encoding this case in your data, i.e. as long as your end timestamp is not really showing the date (or better: showing the real date?), I don't know how you want to handle this.