Set Analysis: non-unique fields

Mon, 15 Dec 2014 22:52:38 GMT

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)

Re: Set Analysis: non-unique fields

rwunderlich — Tue, 16 Dec 2014 02:13:40 GMT

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