topic Re: Display Count if Time is > 30 seconds - Qlikcloud in QlikView

Display Count if Time is > 30 seconds - Qlikcloud

paul_olson — Thu, 06 Jul 2017 20:59:59 GMT

I'm tracking call logs and need to break out calls greater than 30 seconds as well as some other items such as not equal to 00:00:00.

My time field is [Connected Time] and the format is hh:mm:ss. I want this chart to show only the Count([Call ID]) where the [Connected Time] is greater then 30 seconds. I've tried scouring the forumns but haven't found what I'm looking for. I've got this formula so far but it isn't returning the correct results. There are Connected Times of 00:00:00 in there and others that are less then 00:00:30.

I greatly appreciate the help!

=count(if(Interval#(Time([Connected Time],'mm:ss'),'mm:ss') >= Time('00:01','mm:ss') , [Connected Time]))

Re: Display Count if Time is > 30 seconds - Qlikcloud

Anil_Babu_Samineni — Fri, 07 Jul 2017 08:15:07 GMT

Can you try this?

If(Time([Connected Time],'ss') > Time(MakeTime(00:00:30), 'ss'),Count([Call ID]))

If(Time([Connected Time],'ss') > Match(Time([Connected Time],'ss'), '30'),Count([Call ID]))

Re: Display Count if Time is > 30 seconds - Qlikcloud

jonathandienst — Fri, 07 Jul 2017 08:23:24 GMT

This should do it for you:

Count({<[Connected Time] = {">$(=30/86400)"}>} [Call ID])

Explanation: 86400 is the seconds per day. 30/86400 = 30 seconds in date time unit of days

I am assuming that Connected Time is a valid time/date numerical value formatted as hh:mm:ss.

Re: Display Count if Time is > 30 seconds - Qlikcloud

sunny_talwar — Fri, 07 Jul 2017 08:44:02 GMT

May be this

=Count(If(Frac([Connected Time]) >= MakeTime(0, 0, 30), [Connected Time]))

=Count({<[Connected Time] = {"=Frac([Connected Time]) >= MakeTime(0, 0, 30)"}>} [Connected Time])

Re: Display Count if Time is > 30 seconds - Qlikcloud

Kushal_Chawda — Fri, 07 Jul 2017 09:52:16 GMT

create a Flag in script

LOAD *,

If(Frac([Connected Time]) >= MakeTime(0, 0, 30), 1) as [>30SecFlag]

....

Now you can use the Expression like below

=count({< [>30SecFlag]={1}>} [Call ID])

Re: Display Count if Time is > 30 seconds - Qlikcloud

Fri, 07 Jul 2017 11:03:27 GMT

Hi Paul,

First you need to make sure the Connected Time field is an interval field, if not please convert it in the script.

My approach would be to create a variable with the minimum minutes you want to suppress (this way you can reuse it).

Variable value = =Interval#('00:00:30', 'hh:mm:ss')

Then your expression should just simply use the variable:

Count({<[Connected Time]={">$(vIntervalLimit)"}>} [Call ID])

or if you prefer to do it without variable:

Count({<[Connected Time]={">$(=Interval#('00:00:30', 'hh:mm:ss'))"}>} [Call ID])

Tested it myself and it works just fine:

Connected Time	Count all Calls	Count above 30 sec calls
	86536	86514
00:00:00	3	0
00:00:05	1	0
00:00:24	2	0
00:00:27	1	0
00:00:28	1	0
00:00:30	1	0
00:00:31	3	3
00:00:32	1	1
00:00:34	1	1
00:00:35	2	2
00:00:38	1	1

Hope it helps...