topic Problem with Grouped Count in QlikView

Problem with Grouped Count

Thu, 14 Oct 2010 23:38:00 GMT

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:

One of it's ContactDirection must be 1
One of it's IsInQueue must be 1
One of it's ReskillIndicator must be 0 or 1
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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 00:18:35 GMT

Ed,

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

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

Regards,

Problem with Grouped Count

Fri, 15 Oct 2010 00:30:08 GMT

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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 03:18:34 GMT

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,

Problem with Grouped Count

Fri, 15 Oct 2010 04:43:54 GMT

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

Best Regards

Edgardo

Problem with Grouped Count

Fri, 15 Oct 2010 05:13:53 GMT

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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 05:49:13 GMT

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

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

Regards,

Problem with Grouped Count

Fri, 15 Oct 2010 18:04:23 GMT

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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 18:26:38 GMT

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,

Problem with Grouped Count

Fri, 15 Oct 2010 19:28:22 GMT

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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 19:34:14 GMT

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,

Problem with Grouped Count

Fri, 15 Oct 2010 19:54:32 GMT

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

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 19:56:26 GMT

So now the output should be:

31001	0
31002	0
31010	1
31011	0
31012	1

Problem with Grouped Count

Fri, 15 Oct 2010 19:59:14 GMT

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

Regards

-Ed

Problem with Grouped Count

vgutkovsky — Fri, 15 Oct 2010 20:05:09 GMT

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,

Problem with Grouped Count

Fri, 03 Dec 2010 16:05:35 GMT

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

Problem with Grouped Count

vgutkovsky — Tue, 14 Dec 2010 03:12:28 GMT

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,