Handling Null Values

Sisense’s Analytical Engine (AE) is designed to treat NULL values with precision and consistency to ensure that aggregations and expressions return accurate, logically consistent results. This document explains how NULL values behave in calculations and aggregations, how the AE is optimized to interpret different scenarios, how NULL values are displayed, and how to filter them.

Null Values in Calculations

Core Principles

The Sisense Analytical Engine follows these rules for handling NULL:

  • NULL is not equivalent to zero.

  • Arithmetic operations involving NULL (e.g., NULL + 5, 10 * NULL) will return NULL.

  • SUM() will return N/A (displayed in Sisense dashboards) if all input values are either NULL or there are no values.

  • SUM() returns 0 only when 0 is a valid numeric result, not a default fallback for missing data.

  • When combining multiple measures in a single expression, the engine uses internal logic (like the COALESCE function) to handle NULL values more gracefully.

  • Measures are calculated once wherever possible to optimize performance.

Use Case Scenarios

  1. SUM of Empty Columns

    • SUM(column1) where column1 has no rows (empty group):

      Result: N/A

    • This is treated as a single multi-measure expression, and AE uses COALESCE-like behavior to produce 0 where appropriate.

  2. Duplicate Empty Measures

    • SUM(column1) + SUM(column1), where column1 has no values:

      Result: N/A
    • Since the same measure is reused, AE optimizes it into a single measure, so the result is N/A.
  3. NULL vs. Constants
    • SUM(column1), where column1 contains a single NULL value:
      Result: N/A
    • SUM(column1)+5, where column1 contains a single NULL value:
      Result: N/A
  4. Null Propagation in Complex Measures
    • SUM(column1) + SUM(column2), where both column1 and column2 have no values:
      Result: 0

    • This is treated as a single multi-measure expression, and AE uses COALESCE-like behavior to produce 0 where appropriate.

    • SUM(column1) + SUM(column2), where column1 has a single NULL value and column2 has a single valid value (e.g., 2):
      Result: 2

    • The AE recognizes the null in column1, but since column2 returns a valid value, the combined result is computed correctly.

Null Propagation in Complex Measures

For complex measures (e.g., custom metrics combining multiple simple measures or nested logic), if any one component measure returns NULL, the entire measure may return NULL, unless the engine can confidently resolve the rest of the expression.

  • The AE and the previous translator behave similarly in this regard—they attempt to return the best result possible. However, when unable to resolve due to NULL presence, they may return 0 as a fallback in some complex aggregation scenarios.

Arithmetic with NULL

  • 10 * NULL = NULL

  • NULL + 5 = NULL

These follow standard SQL behavior and ensure that NULL-contaminated calculations are not mistakenly interpreted as meaningful numerical results.

Summary Table

Expression

Result

SUM(no values in column1)

N/A

SUM(no values in column1) + SUM(no values in column2)

0

SUM(no values in column1) + SUM(no values in column1)

N/A

10 * NULL

N/A

NULL + 5

N/A

Workaround

If you want to return 0 when the measure is null, you can use the ISNULL function as follows: If(ISNULL(sum(deals)), 0, sum(deals))

For more information, see Using Conditional Statements.

Optimization Notes

  • Single-Measure Optimization: When the same measure appears multiple times (e.g., SUM(column1) + SUM(column1)), the engine optimizes by calculating it once. If that result is NULL, the entire expression returns NULL.

  • Multi-Measure Calculation: When different measures are used in an expression (e.g., SUM(column1) + SUM(column2)), the AE applies additional handling (similar to COALESCE) to produce meaningful results, such as 0 instead of N/A.

This behavior ensures that nulls are respected in isolation but intelligently bypassed when possible in multi-measure aggregations — providing flexibility and clarity in dashboard reporting and data analysis.

Limitations for Null Values in Calculations

  • If you are not using the Analytical Engine, you will experience the old behavior (returning 0 instead of Null).

Displaying and Filtering Null Values

Null values are displayed in all widgets by default. Displaying null values by default provides a more accurate picture of your data. However, in some cases, you may want to filter them. For example, Cartesian charts may appear with gaps in your data that represent your null values.

Below are descriptions of how null values are displayed and how to filter them if necessary.

How Null Values are Displayed in Widgets

Tables
Null values are shown with #N/A

Pivot Tables
Null values are shown with #N/A

Cartesian Charts
In Cartesian charts, null values are displayed as gaps in your data.

Pie Charts

Sisense displays null values in Pie charts in the "Others" slice. The null values are always aggregated to 0% unless replaced using a conditional function (CASE/IF IsNull())

Scatter Charts

On Scatter charts, null values are not displayed. The legend of the chart includes the field member corresponding to the null value.

Tree Maps

On Treemaps, null values are included in the "Others" box.

Sunburst

On Sunburst widgets, null values are not displayed. The legend of the chart includes the field member corresponding to the null value.

Polar Charts

On Polar charts, in place of null values, an empty pole is displayed.

Scatter Maps

On Scatter Maps, null values now appear on the map. The size and color of the corresponding dots on the map are fixed.

Area Maps

On Area Maps, null values are included in the map, however, no visual representation is displayed.

Filtering Null Values

You can filter null values to prevent them from being displayed in your widgets. How you filter them depends on the type of filter.

Below is a list of each filter type, how their Null values are displayed, and how to filter them:

Numeric Filters (Measures/Formulas): Clear the "N\A" from a list filter or by setting an explicit condition on the numeric aggregations (such as X > 0)

Date Filters (Fields/Dimensions): Set a Time Frame, Calendar, or Ranking condition, or exclude 'N\A' using the List filter

String Filters (Fields/Dimensions): In addition to 'N\A' (null) values, empty string values are represented by the string '(blank)'. Both can be filtered out using the list filter (note the known limitations section below regarding conditional filters over strings)

Null and Empty Values in Export to CSV

When you export to CSV, null and empty values are represented as follows:

  • Null values: , ,
  • Empty values: """"""

For example, when downloaded to CSV, a line which contains a "text" field, null, and empty string would look like this: 'text', ," " " " "

Limitations

  • Null values in Weeks does not fade when dashboard filters are set to Highlight
  • Null values are never highlighted in Pie charts
  • Null values are invisible on Area Maps, and therefore cannot be identified