topic Re: Time range problems in QlikView

Time range problems

Thu, 17 Dec 2015 09:42:10 GMT

Hi,

I am working on a qvw where I have to display time durations and time ranges.

What I have available in the data source are a Start_Time field and an End_Time field in DD.MM.YYY hh:mm:ss

So far I have successfully created two duration fields by adding this to the script:

Interval(Time(End_Time) - (Start_Time),'hh:mm:ss') as Duration

Interval(Time(End_Time) - (Start_Time),'ss') as Duration_seconds

No I want to create a time range field which should show the number of elements in a chart with durations of, let´s say

less than 1 hour,

1-3 hours,

everything else

I totally failed with the Duration field, that´s why I created the above Duration_Seconds field as well.

My last useless effort looks like this:

if((Duration_seconds)<=3600,'less than 1 hour',

if((Duration_seconds)<=10800,'1-3hours',

'everything else')) as Range

The result is that all records fall into the less than 1 hour category, even if they lasted 8.000 seconds or more.

I am lost...

Is the seconds field in a wrong format? Can it be converted into numerical? Can it work with the Duration field?

Thanks.

Re: Time range problems

Anonymous — Thu, 17 Dec 2015 18:53:54 GMT

For your Duration_seconds try :

(Time(End_Time) - (Start_Time)) * 24 * 60 * 60 as Duration_seconds

A timestamp is held as the number of days since a certain date ages ago. The integer part of it is the number of days since then and the decimal part is the fraction of a day. It is held as a dual and the text representation is the standard human readable format.

The 'ss' argument to your interval() formats the dual text representation to be human readable seconds, but the dual number part remains as a decimal.

So to get the number of seconds multiply the decimal by the number of seconds in a day, which is 24*60*60..

Re: Time range problems

Fri, 18 Dec 2015 05:22:17 GMT

Bill,

Thank you very much, it works perfectly!

Cheers!