And vs Or

[m_perreault]

I am reviewing someone's script and I cannot tell if an 'And vs Or' situation is creating errors in the data.

The script uses a where clause

'

and (

(Type<>'Fund' and Type<>'Other')

or IsChildPortfolio='False'

) '

Should the script instead by

and (

Type<>'Fund' or Type<>'Other')

or IsChildPortfolio='False'

) '

Is there a difference between the two?

[petter]

Clearly they are different and the two script snippets will give different results.

Just try to calculate manually when Type is equal to 'Fund' and see what you get for both script snippets - different results. For the first parenthesis the first snippet will return false and the second snippet will return true.

So it might be that the logic should be changed.

[m_perreault]

Thanks Petter, could you describe a situation in which you would want to use an AND operation on the same Field?

[petter]

It seems to me that this has to be taken into a larger context and can't be done in isolation.

[m_perreault]

Sure, could you explain how Qlik handles the following.  It seems counter intuitive to me

[petter · Accepted solution]

There is a huge risk with using "bare" fields in any measure in Qlik Sense. You should always (or almost always) use aggregation functions with all field in measures.

If your data returns more than one value for each "row" in a chart/table then a bare field reference will not know what to do with it. So it will turn into a NULL and the full expression might not calculate at all or return the wrong answer.

In your example screenshot above I can't see if multiple values will be returned for each of the fields in your measures.

Henric Cronström has written several blogs/documents about how you should always use an aggregation function and I advice you to read them thoroughly to understand why:

• Use Aggregation Functions!
• It’s all Aggregations
• The Only Function

Even though they are all written in the context of QlikView they are 100% relevant for Qlik Sense....