Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
May be this:
Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} ID)
UPDATE: Sample attached
That works correctly for the ID count (Thanks!)
But the distinct city count is still off.
=Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} City)
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)
Yes, That works!
Thanks