Problem with null values / NOT null values in SET ANALYSIS

[#1]

Hi everybody.

Im still having problems with this expression.

I have to SUM Quantity, with Concept = 'Concept'. And Field has to be NULL.

I'm doing this

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

Now i have to do the same but Field has to be NOT NULL

I'm doing this

SUM({<Concept= {'Concept'},Field-= {""}>}Quantity)

I have the SAME VALUE with the two expresions...

Somebody knows how can i solve this problem?

Thank you.

Greetings.

[alexandros17]

this expression (your first expression gives you all NOT NULL values)

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

the second expression is the same of the first

You can have Null by difference, that is:

SUM({<Concept= {'Concept'},Field= >}Quantity)

-

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

Let me know

[#3]

Hi.

This is kinda strange.

I have a negative result.

And if this is ALL VALUES - NOT NULL this doesn't make sense.

Greetings!

[#4]

I think that SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity) gives me ALL NULL VALUES

I should do this

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

-

SUM({<Concept= {'Concept'},Field= >}Quantity)

what do you think?

[alexandros17]

Field={'*'}-{''} means that you select all values comeing from the first set '*' (are all the values) minus values from the second set '' (no values) ...

Verify your data with a fixed selection

[jerem1234 · Accepted solution]

Hi Jeremias,

    You have to be careful what you refer to as null values. Blanks, blank spaces, and other "empty" strings are not technically null values. They have a value. So what you are doing in:

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

is saying that take everything that has a value in Field (blanks and blank spaces included) and subtracting the set of all blanks from that. So this would be giving you everything that has a value in Field except for blanks (and nulls).

Here are some expressions that may help:

sum({<Field = {'*'}>}Value) - Sum values where Field has some value in it

sum({<Field = {'=len(trim(Field))>0'}>}Value) - Sum where the length of the Field after it is trimmed of spaces is greater than 0 (i.e. everything that has values that aren't blanks or blank spaces).

sum({<Field = {'=len(trim(Field))=0'}>}Value) - Sum where the length of the Field after it is trimmed of spaces is equal to 0. This will sum the blanks and blank spaces in your Field field. This will not include actual null values.

sum({1-$<Field = {'*'}>}Value) - Sum where there are null values. This takes the set 1 (i.e. the whole set of data ignoring selections and subtracting the set of data that has some value in Field. This will give you the actual values where Field is null.

sum({1-$<Field = {'*'}> + $<Field = {'=len(trim(Field))=0'}>}Value) - Sum where there are nulls and blanks and blank spaces.

Please find the attached dashboard for some examples.

Also, NULL handling in QlikView will help. It is written by HIC. Very good read.

Hope this helps!