Null Values in Spectre

Prior to version 7.0(14), most expressions that encountered null would return null, propagating the null all the way up the expression tree. For example, `1 + null` would result in null. Starting with Spectre 7.0(14), null can in many cases be treated as a normal value, like zero or the empty string. Therefore, in 7.0(14) and later, `1 + null` is 1. With this change, Spectre replicates the classic model behavior with nullable summaries.

There are many other differences, which this topic describes in detail, but the biggest changes are:

  • null is sometimes treated as zero or other normal values
  • null displays as blank in summary columns

Null indicates "no data". Here are some ways to get a null value in Spectre:

  • A blank field, that is a zero-length field, in a source file read by Spectre build
  • A value flagged as null using the nulls tag in Spectre build
  • A NULL value in an ODBC table read by Spectre build
  • The result of a summary expression in a dive window using multiple inputs, when the dimension value is not present in one of the inputs (that is, a multi-level multi-model)
  • The result of a summary expression when the working set is empty
  • The expression `null`
  • The expression `""`

You can check for null using the is_null() and notnull() functions, and use the coalesce() function to substitute alternative values for null.

Every null value has a type (integer, fixed100, double, string, date, period, or boolean), based on what the type of the value would be if it were not null. Null behaves differently depending on how it is used and what type it is:

  • For numeric types (integer, fixed100, and double), null is sometimes treated as zero, but there are exceptions depending on how it is used
  • For strings, null is the empty string and vice versa, for example, `"" == null` is true
  • For dates and periods, null is never treated as similar to a normal date or period, it is always considered special
  • For boolean types, null is treated as not true. This is notably different than being false, but in expressions that rely on a boolean result, such as if(), a null value corresponds to false.

When a null is of a numeric type (integer, fixed100, double), the following behaviors apply:

  • The + and - operators treat null as zero, but if both operands are null, the result is null.

    For example, `null + 5` returns 5; `4 - null` returns 4; `null + null` returns null.

  • The * operator returns null if either operand is null.

    For example, `null * 5` returns null.

  • The / operator returns unknown if the denominator is null.

    For example, `5 / null` returns unknown.

  • If the denominator is not null, the / operator treats null numerators as zero.

    For example, `null / 5` returns null.

  • Null is equal to null.

    For example, `null = null` and `null <= null` return true; `null < null returns false.

  • Null is treated as zero in numeric comparisons.

    For example, `null > -10` returns true; 0 = null returns true.

  • The mod() function behaves like the / operator.

    For example, `mod(5, null)` returns unknown; `mod(null, 5)` returns null.

  • The min() and max() functions skip nulls, returning null only if all arguments are null.

    For example, `max(null, -1)` returns -1; `min(null, null, null)` returns null.

  • Math functions that return zero or null when the operand is zero, return null when the operand is null.

    For example, `sqrt(null)`, `round(null)`, and `abs(null)` return null.

  • Other functions treat null as zero.

    For example, `pow(10, null)` returns 1; `log(null)` returns unknown; `pct_var(null, 5)` returns 0.

For strings, null and the empty string ("") are the same. For example:

  • `"x" + null` returns "x"
  • `null = ""` returns true
  • `date("")` and `date(null)` return null
  • `lpad(null, 2, ".")` returns ".."
  • `startswith("xyz", null)` returns true
  • `damerau(null, null)` returns true

When a null is a date or period type, the following behaviors apply:

  • Equality operators consider null dates equal to other null dates.

    For example, `null = null` returns true.

  • Comparisons return false when either argument is null.

    For example, `null > date("2015/01/01")` and `null <= date("2015/01/01")` return false.

  • Functions that return a date when the operand is a date, return null when the operand is null.

    For example, `offset(null, "month", 2)` returns null.

  • Functions that return a boolean when the operand is a date, return false when the operand is null.

    For example, `ytd(null, today())` returns false.

  • Functions that return something else when the operand is a date, return unknown when the operand is null.

    For example, `month_name(null)` and `year(null)` return unknown.

The following behaviors apply to summary functions:

  • The functions count(), ucount(), and dimcount() treat null as just another value, distinct from zero.

    For example, dimcount over [null, "x", "x", null] returns 2.

  • The functions count(), ucount(), and dimcount() return 0 when the working set is empty.

    For example, count over [] returns 0.

  • Other summary functions ignore null values and return null when the working set is empty.

    For example, average over [1, null, 3, null] returns 2; sum of [] returns null; and min of [null, null] returns null.

  • In summary columns, that is the non-dimension columns, null and unknown both display as blank values.
  • In dimension columns, null is the blank value and unknown displays as <unknown>.

See also: