
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
set analysis for multiple condition in dimension
I would like to ensure my pivot table exclude [name] where
[t0 CRR]=[t1 CRR] and
[t0 impr stg] = [t1 impr stg] (row in Red font).
I have tried to use below set analysis in Dimension but it does not work. The result returns one null row.
Would need guidance from experts again. Many thanks as always.
=aggr(only({<[t1 CRR]-=[t0 CRR]>,[t1 Impr Stg]-=[t0 Impr Stg]}[name]),[name])
name | t0 CRR | t1 CRR | t0 Impr Stg | t1 Impr Stg | t1_Amt HKD000 |
230270 | - | 2.1 | - | 0 | 299686.689 |
230270 | 2.1 | - | 0 | - | 0 |
129314 | 2.2 | 2.2 | 0 | 0 | -1.609 |
129314 | 2.2 | 2.2 | 0 | 1 | 8324999.999 |
129314 | 2.2 | 2.2 | 1 | 0 | -1.609 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To compare the values of 2 fields, you will need to use the P() function.
To get Field1<>Field2 AND Field3<>Field4 to work properly in set analysis, you will need to place your minus sign "-" between your set identifier "$" and your set modifiers <> and test where Field1=Field2 AND Field3=Field4. Something like this:
=aggr(only({$-<[t1 CRR]=P([t0 CRR]),[t1 Impr Stg]=P([t0 Impr Stg])>} [name]), [name])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much. I try to replace with your advised code but it still show the same result.
I tried another way to make the population shorter by using
=if((fabs((t1_Amt)-(t0_Amt))>5000000),[Ip Lcl Ref Cde Ith],'<10mn'). But I don't know why those with >35mn is classified as <10mn.
Would appreciate more guidance. Many thanks again.
