topic time calculations within a straight table in QlikView

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 14:15:04 GMT

I dont understand why I'm struggling with this so much but I'm using phone data, in the form of (hh:mm:ss), and I need to do a very simple calculation, average, for each month. I need average ring time/call time/hold time etc.

Everything that I have tried so far just returns a null or gives me "00:00:00". Even when I try to start simple with, sum(ringtime).

Can anyone help me?

time calculations within a straight table

Oleg_Troyansky — Mon, 21 Mar 2011 14:30:24 GMT

We might be able to help, if you could provide a bit more details - what's your data look like? How is it formatted? what expressions are you trying to build? Ideally,if you could post a small sample of your application, we could take a look and diagnose...

One issue to look into - if your phone data provides dates and times in the form or a string, formatted as "hh:mm:ss", you can't do anything with strings. You have to cast the data into a proper date/time or interval data types. For that purpose, we use one of the casing functions:

date#()

timestamp#()

interval#()

You can look them up in the Help Section. Using those functions, you can convert your data into proper time intervals, and then I'm sure you'll be able to operate with it much easier.

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 15:06:21 GMT

Im getting all this data directly form a phone recorder and building it into a QVD.

After uploading it into my script i changed what use to be 'ring time' into: time((ringtime), 'hh:mm:ss') as ring time. And have adjusted all of the subsquent fields the same way.

From there I really thought it would be simple. I set up the application to be filted by date/department/extension etc. and right now all I'm trying to do is set up the ability to average, with the answer in MM:SS format.

I'm not sure if I answered all your questions, so please if you have anymore let me know.

Thanks,

Brandon

time calculations within a straight table

Oleg_Troyansky — Mon, 21 Mar 2011 15:31:58 GMT

Brandon,

everything sounds right... The only thing left is to take a look at your data. If you can post a sample, please do. Otherwise, it's really hard to guess what's wrong...

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 15:37:45 GMT

it wont let me past a screen shot but in my straight table i selected three start times

ST - 8:14:30 - 8:14:31 - 8:14:32

ring time - 00:00:03 - 00:00:00 - 00:00:02

RT(in sec) 3 - 0 - 2

the total mode on both dimensions I have average and I'm getting (-), and 1.6667

the ring time in seconds is giving me the correct answer but so why isnt the ring time? It should just be 00:00:01

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 15:44:41 GMT

I can send you the excel data, how do I upload it?

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 15:56:39 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3857.calls.xls:550:0]

time calculations within a straight table

Oleg_Troyansky — Mon, 21 Mar 2011 16:36:05 GMT

All I can see from Excel is that your "Talk TIme" is a string... I need to see your QVW. You can attach it in the "Options" tab of the message.

time calculations within a straight table

bvann040684 — Mon, 21 Mar 2011 17:32:43 GMT

Unfortunatly that is something I am unable to do.

So I'm assuming, I'm having these trouble because I havent found the correct casing function and when I figure that out it should be straight forward?

time calculations within a straight table

Oleg_Troyansky — Mon, 21 Mar 2011 20:40:35 GMT

right... I suggest trying function interval#() - with the #-sign