topic Re: calculte time in QlikView

calculte time

Anonymous — Tue, 14 Jan 2014 05:49:15 GMT

hi,

i need to calcule time feild - sum(distinct((end time action - start time action))

but when the action start & end not at the same date ( before midnhigt & after midnhigt )

the resolt is not corecct, exm :

start : 22:39 (12-3-13)

end : 00:33 (13-3-13)

resolt :-22.06

what i need to write ?

swuehl — Tue, 14 Jan 2014 06:28:30 GMT

You need to take the date into the calculation, add resp. date to the time to get a datetime:

sum( ([end time action]+[end date action] - ([start time action]+[start date action]) )

(use distinct qualifier if needed, but IMHO it's quite unsual to use it in the sum of time differences).

To be able to use the date as a number, QV needs to parse the dates field in correctly, please read

jagan — Tue, 14 Jan 2014 06:41:21 GMT

Hi,

If you get the result in negative then just 24 add for 1 day, so in your case

start : 22:39 (12-3-13)

end : 00:33 (13-3-13)

resolt :-22.06 + 24 = 1:54

Try this expression

=TimeStamp(If(Timestamp#('00:33', 'hh:mm') - Timestamp#('22:39', 'hh:mm') > 0,

Timestamp#('00:33', 'hh:mm') - Timestamp#('22:39', 'hh:mm') ,

Timestamp#('00:33', 'hh:mm') - Timestamp#('22:39', 'hh:mm') + Timestamp#('24:00', 'hh:mm')), 'hh:mm')

Replace hardcoded Times with your actual fields.

Regards,

Jagan.

preminqlik — Tue, 14 Jan 2014 06:54:38 GMT

hey try this ,

time(timestamp(maketime(left(start,2),right(start,2))-MakeTime(left(endt,2),right(endt,2)),'HH:MM')

Anonymous — Tue, 14 Jan 2014 07:22:41 GMT

hi,

it working good at line,

but where do i need to write sum ?

if i write i get nothing.

thanks.

jagan — Tue, 14 Jan 2014 11:51:53 GMT

Hi,

Can you attach some sample file with data?

Regards,

Jagan.