Problem with Grouped Count

[#1]

Hi, I am having a hard time trying to make a column to show a particular Count, here is the scenario:

I need to count the unique ContactID's that meet the following criterias:

1. One of it's ContactDirection must be 1
2. One of it's IsInQueue must be 1
3. One of it's ReskillIndicator must be 0 or 1
4. One of it's IsAbandon must not be 1

After these filters have been met, then check the total of the sum of its ContactDuration values and compare it against a variable, IF all this is met, then count it.

I have the following data, it has 5 ContactId's just 2 of them should be counted.

ContactID	IsInQueue	ContactDirection	ContactDuration	ReskillIndicator	IsAbandon	VALID
31001		2	1000	0	1
31001		2	0	0	1
31001	1	2	11970	0	1
31001		2	13	0	1
31002			17		1
31002	1		35110		1
31002			17		1
31010		1	1373	0
31010	1	1	80	0
31010	1	1	6140	0
31010		1	3500	0
31010		1	13423	0
31010		1	11924	0
31010		1	30	0
31011		2	4297	0
31011		2	6063	0
31011		2	11924	0
31011		2	46	0
31012		1	11140	0
31012	1	1	11190	0
31012	1	1	5670	0
31012	1	1	2596	0
31012		1	6297	0
31012		1	2233	0
31012		1	3220	0
31012		1	0	0

So far i have came up with a formula that is now returning if the current ContactID is valid, but when i try to compare the sum against the variable it returns all 1's so no further Aggr can be done on it:

//if(

if(
sum(
{1
<
ContactID = P({1<ContactDirection={1}>} ContactID)
* P({1<IsInQueue={1}>} ContactID)
* P({1<ReskillIndicator={"<=1"}>} ContactID)
* E({1<IsAbandon={1}>} ContactID)
,IsInQueue = {1}
>
} ContactDuration
)
, 1,0)

//<=(ServiceLevelAgreement*1000), 1)

I have commented the 1st and last lines because here is were I am stuck now.

The result is the following right now:

ContactID	TEST
31001	0
31002	0
31010	1
31011	0
31012	1
	1

But i want it to be:

ContactID	TEST
31001	0
31002	0
31010	2
31011	0
31012	2
	2

Can someone please take a look at it and throw in any suggestion?

Thanks in advance for your time.

-Ed

[vgutkovsky]

Ed,

This shouldn't be too difficult to do, but a couple questions first:

1. Do you need the count in the script or just in the front end?
2. Why is the count 2 and not 1 for each of the ContactIDs?

Regards,

[#3]

Hi Vlad, thanks for taking a look at my issue, below my comments:

1- The result of this count() will be the value of a column that i'll later reference from another expression

2- I need that all records show the total from all the data, not filtered by the current selection, i tried to make this happen by using 1<{}> but couldnt make it work

[vgutkovsky · Accepted solution]

It seems a little bit strange to me that you want the total count on each ID. This makes the logic somewhat convoluted and much longer than it would need to be otherwise. A sample solution is attached.

Regards,

[#5]

Thank you very much Vlad, tomorrow I'll try to use your fix and bring some joy to our office 🙂

Best Regards

Edgardo

[#6]

Vlad, looking closely, i see that you expression is grouping and filtering correctly, but there is just one piece missing in the puzzle, is that at the end i also need to sum contactDuration for these pre-filtered ContactID's and check if it is greater than a variable, can it be something like this?

if(rowno()<>0,
 if(
 ContactDirection=1
 and max(IsInQueue)=1
 and len(trim(max(ReskillIndicator)))>0
 and min(IsAbandon)<>1
 and sum(ContactDuration)>#SLA //New filter
 ,
 1,
 0
 ),
 1
)

The thing is, that this final sum(ContactDuration) has to be against only the records that have inQueue = 1

Any ideas?

Thanks again for your time Vlad.

-Ed

[vgutkovsky]

Yep, you're close. You just need to add a bit of set analysis:

and sum({<IsInQueue={1}>} ContactDuration) > SLA

Regards,

[#8]

Thanks a lot Vlad, works like a charm, sorry for keep buggin you , but there is just one thing i coulndt get done, i need to further filter the ContactID's by a date variable, I have been trying but it seems like a have some sort of problem with this:

if(
 ContactDirection=1
 and max(IsInQueue)=1
 and len(trim(max(ReskillIndicator)))>0
 and min(IsAbandon)<>1
 and sum({<IsInQueue={1}>} ContactDuration) <=(ServiceLevelAgreement*1000)
 and ContactStartDate > num(varCalendarStartDate) > num(varCalendarEnd) // doesnt work
 ,
 1
 )

this new filter is that a date column, must fit between a range of 2 variables.

Any suggestions?

-Ed

[vgutkovsky]

You can't combine comparisons that way in QlikView. So you would need to use the following logic: and ContactStartDate > num(varCalendarStartDate) and ContactStartDate < num(varCalendarEnd) or something similar. If ContactStartDate is the same for all rows within the ContactID, then you can also just add the following set analysis to the outer sum: <ContactStartDate={">$(varCalendarStartDate)<$(varCalendarEnd)"}>

Regards,

[#10]

I tried your code, but didnt work:

if(
 ContactDirection=1
 and max(IsInQueue)=1
 and len(trim(max(ReskillIndicator)))>0
 and min(IsAbandon)<>1
 and sum({<IsInQueue={1},ContactStartDate={">=$(=num(varCalendarStartDate))<$(=num(varCalendarEndDate))"}>} ContactDuration) <=(ServiceLevelAgreement*1000)
 ,
 1
 )

The problem is that is not filtering further, i think the reason is because the ContactStartDate is not the same inside the ContactID context i tried a min() but didnt work too:

 sum({<IsInQueue={1},Min(ContactStartDate)={">=$(=num(varCalendarStartDate))<$(=num(varCalendarEndDate))"}>} ContactDuration) <=(ServiceLevelAgreement*1000)

Any suggestion?

-Ed