Announcements
cancel
Showing results for
Did you mean:
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
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
2 Replies
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
Contributor III
Author

Thanks, that's work!

Tags
Community Browser