Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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