# QlikView App Development

Discussion Board for collaboration related to QlikView App Development. Partner

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

Lets say i have two tables:

 Join Dim X Value X 1 A 10 2 A 10 3 B 10 4 C 10 5 C 10 6 C 10

 Join Dim Y Value Y 1 P 10 2 P 10 3 Q 10 4 Q 10 5 Q 10 6 R 10

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? Regards

SJ

Tags (1)
1 Solution

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

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

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

Perfecto, thanks a lot!  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

## 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

## 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

## 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

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

i agree, got the poin