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

[imsushantjain]

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

[tresesco · Accepted solution]

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]

Perfecto, thanks a lot!

[imsushantjain]

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

[tresesco]

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]

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]

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]

i agree, got the poin