Set expression giving wrong value

[su_pyae]

Hi everyone, 

I am using a set expression similar like this: 

=Sum({$<Col_1 = {'55'}, Col_2= {'Brand1', 'Brand2', 'Brand3'}, Col_3= {'Text'}>} Amount)

It is giving me the value when Col_3 doesn't have 'Text' in Col_3. 

It is supposed to give me zero when Col_3 doesn't have 'Text' in that column, right? 
I am a bit confused as to why it is happening. 

Thank you for your help in advance. 

[rubenmarin1]

Hi, yes, it should return 0 on rows where Col_3<>'Text', if some row is returning something differrent you can check adding Col_3 as dimension to confirm the value, maybe the data model makes some unexpected relations and it ends having value.

[marcus_sommer · Accepted solution]

In general the set analysis will work in the way you designed it and what you expect as results. An exception happens if a field hasn't any field-value else has only NULL. In this case it doesn't exists a state-value which could be evaluated and this condition will be ignored as if it weren't placed there.

Handling NULL could become quite complicated within Qlik and therefore it's most the simplest way to replace NULL with real values within the script. Quite common are approaches like:

if(len(trim(Field)), Field, 'NULL') as Field // each value even an empty string will fulfill the purpose

- Marcus

[rwunderlich]

it would only give zero if all rows that match the other conditions did not have 'Text' in column three. 

A simple confirmation (you may have already done this)If you make a table that contains the dimensions Col_1, Col_2, Col_3.  Then make selections in Col_1 and Col_2 same as your set expression. Any rows with 'Text' in Col_3?

-Rob

[su_pyae]

Thank you for your help. It has NULL value and I think it's why the calculations are off. 

[su_pyae]

Thank you for your help. Col_3 has null values while Col_1 and Col_2 do have the rows which fulfill their conditions. Null values might be messing with the calculation like Marcus has mentioned in his reply. 

[su_pyae]

Thank you. I didn't know that Null value can be that problematic. 🙂