topic Re: mean time between date & time in QlikView

mean time between date & time

Anonymous — Tue, 08 Mar 2016 09:01:59 GMT

hi,

i need to calculte rest time .

this time is calculte with 3 feilds:

1. date

2. in - start time

3. out - end time

i need to calculte the time between date & out and date & in

exm:

date	in	out	rest time
01/03/16	08:00	17:00
02/03/16	09:00	16:00	16:00
04/03/16	08:00	21:00	40:00
05/03/16	05:00	14:00	08:00

hope it's understood.

thanks'

yarin.

Re: mean time between date & time

kaushiknsolanki — Tue, 08 Mar 2016 09:08:42 GMT

Hi,

Can you explain how did you got rest values.

What is the logic.

Regards,

Kaushik Solanki

Re: mean time between date & time

tresB — Tue, 08 Mar 2016 09:49:45 GMT

Try this:

Load

Interval(Date#(date&in,'DD/MM/YYhh:mm')-Date#(Previous(date)&Previous(out),'DD/MM/YYhh:mm'),'hh:mm') as CalculatedrestTime;

LOAD date,

in,

out,

[rest time]

FROM

[https://community.qlik.com/thread/208549]

(html, codepage is 1252, embedded labels, table is @1);

Re: mean time between date & time

Anonymous — Tue, 08 Mar 2016 11:51:56 GMT

hi,

i'm geting closer ... but i get previos date not o.k.

mybe i need to sort the table at script by date ?

how do i do this ?

Re: mean time between date & time

tresB — Tue, 08 Mar 2016 11:54:28 GMT

Load * From <> Order By Date;

? May be.

Re: mean time between date & time

sunny_talwar — Tue, 08 Mar 2016 11:55:12 GMT

You can do something like this (Using tresesco‌ code)

Table:

LOAD date,

in,

out,

[rest time]

FROM

[https://community.qlik.com/thread/208549]

(html, codepage is 1252, embedded labels, table is @1);

NewTable:

LOAD *,

Interval(Date#(date&in,'DD/MM/YYhh:mm')-Date#(Previous(date)&Previous(out),'DD/MM/YYhh:mm'),'hh:mm') as CalculatedrestTime

Resident Table

Order By date;

DROP Table Table;