Set expression Exclude field value that matches selected values in another field

[JohanLind1979]

Dear community experts, I´m trying to exclude records based on field values that matches. Selections are made in FieldA and whatever selections that are made in FieldB, records where FieldA=FieldB should be ignored. 

Examples for table below:

1. If 'A' is selected in FieldA, then Sum(FieldToCalculate) should be =2 (regardless any selections, or no selections in FieldB)

2. If 'B' is selected in FieldA, then Sum(FieldToCalculate) should be =8 (regardless any selections, or no selections in FieldB)

3. If 'C' is selected in FieldA, then Sum(FieldToCalculate) should be =6 (regardless any selections, or no selections in FieldB)

4. If  'B' and 'C' are selected in FieldA, then Sum(FieldToCalculate) should be =14 (regardless any selections, or no selections in FieldB)

FieldA	FieldB	FieldToCalculate
A	A	1
A	B	2
B	A	3
B	B	4
B	C	5
C	B	6
C	C	7
C	C	8

I´ve tried this but it doesn´t seem to work: Sum( {$< FieldB -= {"$(=getfieldselections(FieldA))"}>} FieldToCalculate)

[Oleg_Troyansky]

Hi Johan,

To satisfy your first 3 examples, you could use the function E() to exclude values that are selected in column A:

FieldB = E(FieldA)

However, your 4th example throws it off - in this example, you are not just excluding the values that are selected in FieldA, but your formula needs to be sensitive to the specific combination of chart dimensions (FieldA and FIeldB). Only those rows where the two values are identical, need to be excluded. 

Unfortunately, Set Analysis cannot help in this situation - it cannot be sensitive to individual Dimension values, because it's calculated for the whole dataset, outside of your chart. So, you have two options:

1. If you can calculate the condition A=B in the data load script and set a flag to 1 or 0, then you can include the flag in your set analysis conditions. That would be OK for set analysis.

2. Otherwise, you would have to resort to the dreaded IF() function in your formula and test the condition there. 

The only other option, which I can only guess, would be to invest more energy in data modeling and to associate the two fields directly in the data model in such a way that this logic would get calculated automatically. I hope my explanation makes sense to you.

If you'd like to learn advanced uses of Set Analysis, check out my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik - we will be in Dublin, Ireland in October.

Cheers,

[jonashertz]

Easiest would be to add a flag to your script when FieldA <> FieldB:

Load

     IF(FieldA<>FieldB,1) as flgInclude

From 

Then your expression simply needs to ignore selections in FieldB and calculate all flgInlcude values:

sum({<FieldB=, flgInclude={1}>} FieldToCalculate)

[JohanLind1979]

Thank´s, I actually couldn´t even get FieldB = E(FieldA) to work properly for the first three cases. I´ve tried getfieldselections(), concat(), E(), Minus sign. Nothing seems to end up the way I want it. If statement works, so maybe I´ll go for that, even if I really wanted to avoid it. Flag would be best of course but the app/script is kind of a monster so I´ll try with if() first.

[JohanLind1979]

Thank´s Jonas, totally agree but will use if statement without the flag this time due to the size and complexity of the app.

[jonashertz · Accepted solution]

Then we need to keep the comparison of FieldA and B outside the set analysis since we cannot lock FieldB in a boolean condition. This one is less easy to read but it Ignores selections in FieldB and the inner condition evaluates true only when FieldA <> FieldB: 

=sum({<FieldB=>} FieldToCalculate * (-1)*(FieldA<>FieldB) )

Edit: but maybe this is just the same as an IF statement anyhow..

[JohanLind1979]

Wow, that was absolutely fantastic! Didn´t know you could do that! Thank´s a million Jonas! Would you like to explain the (-1)?

With this solution I guess you could also skip the {<FieldB=>} part if you want the report to follow selections in FieldB. It still only uses the records where the field values don´t match.

[jonashertz]

The (-1) is to there because boolean expressions in Qlik return either 0 (false) or -1 (true).

Yep you can remove the FieldB= to have the formula react to selections in FieldB.

[JohanLind1979]

Think I understand a little more now. Also what you meant with the boolean condition. 

Sum(FieldA=FieldB)  evaluates every record and sets 1 (or actually -1) to every record where the values in the fields match, and 0 if the values don´t match. 

Sum(FieldA<>FieldB)  evaluates every record and sets 1 (or actually -1) to every record where the values in the fields don´t match, and 0 if the values match. 

Multiplying those 1's with FieldToCalculate gives the correct sum. 

What I still don´t understand is why it´s negative (-1), which forces us to also multiply with -1?