Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bbastro1106
Contributor III
Contributor III

Include Null value and exclude particular value in set analysis

Hi,

I was trying to sum up a field "PO total Units".  

i used formula like the following:

Sum( {$<[PO Line Status Code] -={9}>} [PO Total Units])

However, under the PO line status code, there are value like "-" (Null) which i found all those number not sum up with this formula.

Is there anyone can help with this?  I do not want to add any flag into my script at this stage, i would like to include this in the expression formula.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

The set expression works like a manual selection and you cannot select nulls directly. There are several workarounds, such as NULL AS VALUE or a flag field. Or just a simple addition to the expression:

Sum({$<[PO Line Status Code] -= {9}>} [PO Total Units]) +
Sum(If(IsNull([PO Total Units])))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

The set expression works like a manual selection and you cannot select nulls directly. There are several workarounds, such as NULL AS VALUE or a flag field. Or just a simple addition to the expression:

Sum({$<[PO Line Status Code] -= {9}>} [PO Total Units]) +
Sum(If(IsNull([PO Total Units])))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
bbastro1106
Contributor III
Contributor III
Author

Thanks, that's work!