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: 
imsushantjain
Partner - Creator
Partner - Creator

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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
tresesco
MVP
MVP

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


imsushantjain
Partner - Creator
Partner - Creator
Author

Perfecto, thanks a lot!

imsushantjain
Partner - Creator
Partner - Creator
Author

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

tresesco
MVP
MVP

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.

imsushantjain
Partner - Creator
Partner - Creator
Author

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'}> .

tresesco
MVP
MVP

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.

imsushantjain
Partner - Creator
Partner - Creator
Author

i agree, got the poin