Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Aggr Syntax

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

1 Solution

Accepted Solutions
sergio0592
Specialist III
Specialist III

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)

View solution in original post

7 Replies
swuehl
MVP
MVP

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)

sergio0592
Specialist III
Specialist III

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)

cbaqir
Specialist II
Specialist II
Author

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? !

swuehl
MVP
MVP

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

cbaqir
Specialist II
Specialist II
Author

It still shows 1 for each COM_CAT:

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

11-29-2017 2-32-20 PM.jpg

swuehl
MVP
MVP

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?

swuehl
MVP
MVP

Or maybe try

=Sum(

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