Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
jason_nicholas
Contributor II

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

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.

5 Replies
jason_nicholas
Contributor II

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

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.

MVP
MVP

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

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

MVP
MVP

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

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?

Highlighted
jason_nicholas
Contributor II

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

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.

MVP
MVP

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

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.