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]

No, the outer sum, not the inner sum--put the set analysis on the sum that encloses the aggr. If that doesn't work, can you repost your original table but the Start date column so I can see what it looks like?

Regards,

[#3]

Worked perfectly Vlad, thank you very much, this was the final version of the expression, the requirement changed, so no longer the total must be shown in all the filtered ContactID's

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

[vgutkovsky]

So now the output should be:

31001	0
31002	0
31010	1
31011	0
31012	1

?

[#5]

Yes you are right, thanks again for all you time on this.

Regards

-Ed

[vgutkovsky]

Actually, that makes the whole expression easier:

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

Regards,

[#7]

Hi Vlad, long time since you helped me to fix this.

Recently we deplyed our initial version of the QV document, and under heavy loads of data, we are seeing that this speciffic metric is hitting hard our CPU, we think AGGR is a costly operation, do you think the same? If so, do you think there is another way to pull this expression without using AGGR?

Regards.

-Ed

[vgutkovsky]

Hi, Ed. AGGR is definitely a very heavy operation. Unfortunately in your case there is no other way to do it given your current fields. That is because you need to do nested aggregation and that is only possible with AGGR. To improve performance, I would recommend moving a lot of the aggregation to the script and creating a flags that you can then check using set analysis. For example (in a Group By) you can check min(IsAbandon)<>1 and set a flag of 1 for instances where this is true.

Regards,