Set Analysis: non-unique fields

[#1]

Hi,

I want to write a calculated dimension to only select  rows with duplicate ID values and negative Amount. So in the table below:

Key   ID     Amount

1,      A,      -10

2,      B,      -15

3,      C,       2

4,      A,      -12

5,      B,      14

6,     A,       2

7,     D,      -33

the calculated dimension of a chart should only pull in Keys: 1,2,4.  How would you go about writing the set analysis. Something like so:

=aggr(only({<ID= P({<Amount={"<0"}>} ID )> * <ID=P({<the magic to select rows with duplicate IDs>} ID)>} ID) ,ID)

[rwunderlich]

I'm not sure you need the P() operator here. I would write it:

=aggr(

  only({

  <ID={"=min(Amount)<0"}>

  * <ID={"=count(ID)>1"}>

  }

  ID)

,ID)

-Rob

http://masterssummit.com

http://robwunderlich.com