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 returnNULL
. -
SUM()
will returnN/A
(displayed in Sisense dashboards) if all input values are eitherNULL
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 handleNULL
values more gracefully. -
Measures are calculated once wherever possible to optimize performance.
Use Case Scenarios
-
SUM of Empty Columns
-
SUM(column1)
wherecolumn1
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.
-
-
Duplicate Empty Measures
-
SUM(column1) + SUM(column1)
, wherecolumn1
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
.
-
- NULL vs. Constants
SUM(column1)
, where column1 contains a singleNULL
value:Result: N/A
SUM(column1)+5
, wherecolumn1
contains a singleNULL
value:Result: N/A
- 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)
, wherecolumn1
has a singleNULL
value andcolumn2
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 |
|
N/A |
|
0 |
|
N/A |
|
N/A |
|
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 isNULL
, the entire expression returnsNULL
. -
Multi-Measure Calculation: When different measures are used in an expression (e.g.,
SUM(column1
) +SUM(column2)
), the AE applies additional handling (similar toCOALESCE
) 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