topic Re: set analysis for multiple condition in dimension in App Development

set analysis for multiple condition in dimension

43918084 — Mon, 20 Dec 2021 21:02:46 GMT

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

Re: set analysis for multiple condition in dimension

GaryGiles — Sat, 27 Feb 2021 12:50:36 GMT

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])

Re: set analysis for multiple condition in dimension

43918084 — Sat, 27 Feb 2021 17:21:10 GMT

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.