calculte time

[Anonymous]

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]

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

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

[jagan]

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]

hey try this ,

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

[Anonymous]

hi,

it working good at line,

but where do i need to write sum ?

if i write i get nothing.

thanks.

[jagan]

Hi,

Can you attach some sample file with data?

Regards,

Jagan.