Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

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

namet0 CRRt1 CRRt0 Impr Stgt1 Impr Stgt1_Amt HKD000
230270-2.1-0299686.689
2302702.1-0-0
1293142.22.200-1.609
1293142.22.2018324999.999
1293142.22.210-1.609
2 Replies
GaryGiles
Specialist
Specialist

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

43918084
Creator II
Creator II
Author

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.

10mn.PNG