topic Re: Aggr Syntax in QlikView

Aggr Syntax

cbaqir — Wed, 29 Nov 2017 20:27:45 GMT

I am trying to ID the count of DEFECT_ID that have never been COM_CAT = 'ERC - Routing'. Everything I am trying is only excluding the one row that COM_CAT = 'ERC - Routing' as opposed to showing those that have never been in that status. What is the right syntax for this?

aggr(Count({$<COM_CAT-={'ERC - Routing'}>} distinct DEFECT_ID), DEFECT_ID,COM_CAT,REQUEST_REGION,CUR_STATUS)

stalwar1

Re: Aggr Syntax

swuehl — Wed, 29 Nov 2017 20:34:30 GMT

Maybe check your other post (and I would appreciate to stick to one thread).

And maybe attach some sample records and your expected result. It's not 100% clear to me what you are trying to achieve.

edit: your other thread seems to be deleted. This was my suggestion:

Count({$<DEFECT_ID = e({<COM_CAT ={'ERC - Routing'}>}) >} distinct DEFECT_ID)

Re: Aggr Syntax

sergio0592 — Wed, 29 Nov 2017 20:35:51 GMT

Hi, i think you can use E() function.

aggr(Count({$<COM_CAT= E({1 <COM_CAT={'ERC - Routing'}>})>} distinct DEFECT_ID), DEFECT_ID,COM_CAT,REQUEST_REGION,CUR_STATUS)

Re: Aggr Syntax

cbaqir — Wed, 29 Nov 2017 21:16:08 GMT

Stefan,

Thanks for your response. I actually did delete that comment before writing this one. Apologies.

Here is the attached sample. I am looking for a distinct count of DEFECT_ID for those that did not EVER have a COM_CAT = 'ERC - Routing'. While the expression with e appears to get me closer, it's showing a count of 1 for each COM_CAT as opposed to 1 per distinct DEFECT. Any thoughts? !

Re: Aggr Syntax

swuehl — Wed, 29 Nov 2017 21:21:48 GMT

Have you tried my expression (the one without advanced aggregation)?

Re: Aggr Syntax

cbaqir — Wed, 29 Nov 2017 21:32:39 GMT

It still shows 1 for each COM_CAT:

Count({$<DEFECT_ID = e({<COM_CAT ={'ERC - Routing'}>}) >} distinct DEFECT_ID)

Re: Aggr Syntax

swuehl — Wed, 29 Nov 2017 21:37:57 GMT

That's because you are using COM_CAT as dimension.

The Total line should be correct, displaying the distinct count of Defect IDs.

What do you expect to see in your table?

Re: Aggr Syntax

swuehl — Wed, 29 Nov 2017 22:38:46 GMT

Or maybe try

=Sum(

Aggr(Count({$<DEFECT_ID= E({1 <COM_CAT={'ERC - Routing'}>})>} distinct DEFECT_ID), DEFECT_ID,REQUEST_REGION,CUR_STATUS) )