topic Re: Subtracting times in QlikView

Subtracting times

Anonymous — Tue, 05 May 2015 12:26:28 GMT

I have a field that calculates an average call time.

From this value, I want to take away 3 mins 30 seconds into a text box. What is the correct expression to use?

Currently I've got:

=time(Avg(CallTime),'hh:mm:ss')-(time(003330),'hh:mm:ss')

which obviously isn't working!! Hope you can help!

sunny_talwar — Tue, 05 May 2015 12:29:30 GMT

Slight Change: 3 mins and 30 seconds will be 000330 instead of 003330

Try this:

=Interval(Interval#(CallTime,'hh:mm:ss') - Interval#(000330,'hhmmss'), 'hh:mm:ss')

sunny_talwar — Tue, 05 May 2015 12:30:52 GMT

Posting a document where I used it in a text box with CallTime = '10:52:30'

Anonymous — Tue, 05 May 2015 13:10:46 GMT

I've tried the expression, but still coming up with an error. I've tried with both interval# and time#

buzzy996 — Tue, 05 May 2015 13:12:51 GMT

try tis,

=Interval(Interval(Sum(ProProductiveTime),'hh:mm:ss') -Interval(Sum(ProNonProductiveTime),'hh:mm:ss'),'hh:mm:ss')

sunny_talwar — Tue, 05 May 2015 13:23:44 GMT

Can you share a sample of where is not working?

Best,

Sunny

Anonymous — Tue, 05 May 2015 13:29:04 GMT

I've just tested displaying 000330 in a time format.

=time(000330,'hh:mm:ss')

This returns a text box of 00:00:00. So the only point of failure is trying to get the 3 minutes and 30 seconds to display correctly.

sunny_talwar — Tue, 05 May 2015 13:30:53 GMT

Try this:

=Interval(interval#(000330, 'hhmmss'),'hh:mm:ss')

It will display:

Anonymous — Tue, 05 May 2015 13:34:52 GMT

Perfect!

=interval(time(avg(calltime),'hh:mm:ss') - interval#(000330,'hhmmss'),'hh:mm:ss')

Gives me exactly what I need

Thans for your help!

sunny_talwar — Tue, 05 May 2015 13:36:14 GMT

That's what I kind of gave earlier also. It didn't have Avg() in it. But I am glad it all worked out for you in the end.

Best,
Sunny