topic Re: Time Format Over 24 hours in QlikView

Time Format Over 24 hours

Tue, 03 Jan 2012 14:58:28 GMT

I have a text box which shows the hours between the time an incident was logged until it was closed. One of the incidents is showing a time of 76 Hours, 50 Minutes and 43 Seconds. In the text box it should show as 76:50:43, however it shows as 04:50:43 (which is 3 days, 4 hours, 50 mins and 43 secs).

How do I get the text box to show just the hours (ie: 76:50:43) without counting the days.

Regards,

Jon Ditchfield

P.S. I am a novice at Qlikview and only have an QV Personal Edition

Time Format Over 24 hours

swuehl — Tue, 03 Jan 2012 15:03:26 GMT

Please look into the interval() function which is capable of handling durations > 24h.

Re: Time Format Over 24 hours

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 15:13:55 GMT

Hi,

Assuming you have a Start and End date fields, the Interval() function with the substraction of one timestamp to the other should work. For example, this expression

=Interval(Timestamp('03/01/2012 22:00:00') - Timestamp('01/01/2012 21:30:00'))

Should return "48:30:00" meaning 48 hours, 30 minutes, zero secods. The first Timestamp would be the end date, and the second the start date.

Note that is very important the format the date field is, and make sure to do any required formatting in the script.

Hope that helps.

Miguel

Re: Time Format Over 24 hours

Tue, 03 Jan 2012 15:23:47 GMT

Hi,

The calculation have already been worked out in excel and then brought over to Qlikview so I only have the time between the call being logged and resolved (ie: 23 hours, 40 minutes in format 23:40:00) not a start and end time in qlikview.

Re: Time Format Over 24 hours

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 15:32:02 GMT

Hi,

Interval() will do likewise. The following will work in a text object

=Interval('48:30:00', 'hh:mm:ss')

Hope that helps.

Miguel

Re: Time Format Over 24 hours

Tue, 03 Jan 2012 15:45:28 GMT

Hi Miguel,

Thanks for the reply.

Where would I need to enter this into, I have tried entering it in Text Box Properties, the 'sort' tab, then I picked up the sort priority, which I called Average Call Time, then ticked expression and then entered the expression. It still shows the Average call time as 04:50:43, rather than 76:50:43.

Re: Time Format Over 24 hours

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 16:02:42 GMT

Hi,

You should use that expression in the load script, as in this example:

Data:
LOAD Interval(AverageCallTime) AS AverageCallTime
INLINE [
AverageCallTime
78:45:00
13:00:00
145:00:00
];

Now the field AverageCallTime should show properly and should be sorted numeric properly as well.

Hope that helps.

Miguel

Re: Time Format Over 24 hours

Mon, 03 Oct 2016 15:40:21 GMT

I know this question was ages ago but just in case somebody googled it as I have did the answer is as follows:

try changing the details of the visuals on the side as follows :