Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
Perfecto, thanks a lot!
My expression is working as OR, and I need AND. Your solution works perfectly in this case
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.
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'}> .
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.
i agree, got the poin