topic Calculate hours between time intervals in QlikView

Calculate hours between time intervals

qw_johan — Thu, 22 Aug 2013 06:21:25 GMT

I need to calculate how many hours there are between a persons work shifts in a

schedule, his "resting time" so to speak.

My table is simple: id_number, timestamp_from, timestamp_to.

My code so far...

LOAD

id_number,

timestamp_from,

timestamp_to,

Interval(Previous(timestamp_to) - timestamp_from, 'hh:mm') as rest_time

FROM ....

Problem...

The resulting hours in rest_time is a negative number...how to make it into a positive number?

Thanks

Re: Calculate hours between time intervals

kumarnatarajan — Thu, 22 Aug 2013 06:26:12 GMT

Hi,

Use Fabs() function for positive.

But check your timestamp fields format Interval() function will return correct hours.

Re: Calculate hours between time intervals

jagan — Thu, 22 Aug 2013 06:30:54 GMT

Hi,

Just change from and to column positions in Load script..

LOAD

id_number,

timestamp_from,

timestamp_to,

Interval(timestamp_from - Previous(timestamp_to) , 'hh:mm') as rest_time

FROM ....

Regards,

Jagan.

Re: Calculate hours between time intervals

Thu, 22 Aug 2013 06:34:02 GMT

fabs(x)

The absolute value of x. The result is a positive number.

Examples:

fabs( 2.4 ) returns 2.4

fabs( -3.8 ) returns 3.8

*******************************************

or subtract greater-smaller

Re: Calculate hours between time intervals

er_mohit — Thu, 22 Aug 2013 06:49:58 GMT

try this way

Interval(timestamp_to - timestamp_from, 'hh:mm') as rest_time

Re: Calculate hours between time intervals

Thu, 22 Aug 2013 08:15:04 GMT

Hi,

Please find the attached file. I hope it helps you

Thanks and Regards,

S. Amuthabharathi

Re: Calculate hours between time intervals

qw_johan — Thu, 22 Aug 2013 18:18:44 GMT

Thanks 🙂 it helped