Display Count if Time is > 30 seconds - Qlikcloud

[paul_olson]

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]))

[Anil_Babu_Samineni]

Can you try this?

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

OR

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

[jonathandienst]

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.

[sunny_talwar]

May be this

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

or

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

[Kushal_Chawda]

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])

[#6]

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...