Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

How to exclude dimension vales in set formula with AND clause?

Lets say i have two tables:

JoinDim XValue X
1A10
2A10
3B10
4C10
5C10
6C10

JoinDim YValue Y
1P10
2P10
3Q10
4Q10
5Q10
6R10

Now I want to Calculate Sum(Value Y) where Dim X is not 'B' and Dim Y is not 'Q'  . Hence I use this formula:

=SUM({$<[Dim X]-={'B'},[Dim Y]-={'Q'}>}[Value Y])

Which gives me value 30,  but that's incorrect as it is executing OR condition.

I am looking for ''where Dim X is not 'B' and Dim Y is not 'Q' ", which should give me a value 50.


In other words, i want to exclude records where Dim X ='B' and Dim Y ='Q', and then calculated Sum([Value Y]) which is 50. How to do it?


exclude values.jpg

Regards

SJ

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to exclude dimension vales in set formula with AND clause?

I guess, you are getting it exactly opposite of the behavior. The expression is working as AND, and you need OR. Try like:

=SUM({$<[Dim X]-={'B'}>+<[Dim Y]-={'Q'}>}[Value Y])


View solution in original post

7 Replies
MVP
MVP

Re: How to exclude dimension vales in set formula with AND clause?

I guess, you are getting it exactly opposite of the behavior. The expression is working as AND, and you need OR. Try like:

=SUM({$<[Dim X]-={'B'}>+<[Dim Y]-={'Q'}>}[Value Y])


View solution in original post

Partner
Partner

Re: How to exclude dimension vales in set formula with AND clause?

Perfecto, thanks a lot!

Partner
Partner

Re: How to exclude dimension vales in set formula with AND clause?

My expression is working as OR, and  I need AND. Your solution works perfectly in this case

MVP
MVP

Re: How to exclude dimension vales in set formula with AND clause?

Any confusion?

Let me repeat and emphasis - if you write set like : <FieldA={'A'}, FieldB={'X'}> that means it is equivalent to If( FieldA='A' AND FieldB='X'). If you need to use OR, i.e.  If( FieldA='A' OR FieldB='X')., set equivalent would be   <FieldA={'A'}>+<FieldB={'X'}> .

<FieldA={'A'}, FieldB={'X'}> ~ If( FieldA='A' AND FieldB='X')

<FieldA={'A'}>+<FieldB={'X'}> ~ If( FieldA='A' OR FieldB='X')

Hope this is clear now.

Partner
Partner

Re: How to exclude dimension vales in set formula with AND clause?

Well, the way i see it working for me in QlikSense is :

<FieldA={'A'}, FieldB={'X'}> that means it is equivalent to If( FieldA='A' OR FieldB='X').


If you need to use AND, i.e.  If( FieldA='A' AND FieldB='X').,

then set equivalent would be   <FieldA={'A'}>+<FieldB={'X'}> .

MVP
MVP

Re: How to exclude dimension vales in set formula with AND clause?

I believe you are being confused, because you are using negative operator (that is exclusion) in set. Try with normal (inclusion - no negative sign) set analysis; that would clear your doubt.

Partner
Partner

Re: How to exclude dimension vales in set formula with AND clause?

i agree, got the poin