How to use aggr to get count of values associated to a duplicate?

[nikita42]

Hi, I have a table like this:

Case	ID	City
1	A	NY
2	B	NY
2	C	NY
3	D	LON
4	E	NY
4	F	NJ
5	G	LON

I've managed to get the count of cases that have multiple IDs associated to it using the expression:

Count(If(Aggr(Count(ID),Case)>=2,Case)

and this gives me the correct result - 2

Now I need to get the count of IDs and cities associated with these cases. Basically I need to show that these 2 cases are duplicated across 4 people.

I've tried changing the expression to Count(If(Aggr(Count(ID),Case)>=2,ID), but this still gives me the count of cases because the aggr table doesn't provide a row for each ID.

Similarly, I need to get a count of the number of unique cities that have a duplicate case. In this example only case 4 has 2 distinct cities, so I need the result to be 2.

Any ideas?

[sunny_talwar]

May be this:

Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} ID)

UPDATE: Sample attached

[nikita42]

That works correctly for the ID count (Thanks!)

But the distinct city count is still off.

[sunny_talwar]

=Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} City)

[swuehl · Accepted solution]

Or maybe, if you want to exclude case 2 (which in this case shows same City then case 4, but could be different):

=Count(DISTINCT {<Case = {"=Count(DISTINCT City) >= 2"}>} City)

[nikita42]

Yes, That works!

Thanks