IS_VALID
Description
IS_VALID
is a row function that returns 0
if the
returned value is NULL
, and 1
if the returned value is
NOT NULL
. This is useful for computing other calculations where you
want to exclude NULL values (such as when computing averages).Syntax
IS_VALID
(expression
)Return Value
Returns
0
if the returned value is NULL
, and
1
if the returned value is NOT NULL
.Input Parameters
- expression
- Required. A field name or expression.
Examples
Define a Prism calculated field using
IS_VALID
. This returns a row
count only for the rows where this field value is NOT NULL
. If a value
is NULL
, it returns 0 for that row. In this example, we create a Prism
calculated field (sale_amount_not_null
) using the sale_amount
field as the
basis. IS_VALID([sale_amount])
Then you can use the
sale_amount_not_null
Prism calculated field to calculate an
accurate average for sale_amount
that excludes NULL
values:SUM([sale_amount])/SUM([sale_amount_not_null])