Aggr Syntax

[cbaqir]

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

[swuehl]

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 · Accepted solution]

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]

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]

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

[cbaqir]

It still shows 1 for each COM_CAT:

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

[swuehl]

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]

Or maybe try

=Sum(

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