# Handling 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.

*Source: https://docs.sisense.com/main/SisenseLinux/handling-null-values.htm*

---

Last updated: June 11, 2026

|  |  |
| --- | --- |
| [Tier](https://www.sisense.com/pricing/#pricing) | [Deployment](https://docs.sisense.com/main/SisenseLinux/introduction-to-sisense-cloud-managed-services.md#ComparisonofManagedCloudandSelfHosted) |
| Launch     Grow    Enterprise | Cloud     On-Prem |

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](https://docs.sisense.com/main/SisenseLinux/conditional-statements.md).

### 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.
  
![Handling Null](https://docs.sisense.com/main/Resources/Images/Handling Null.png)

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

### How Null Values are Displayed in Widgets

**Note:**

Empty string values in chart dimensions and categories are represented by the string ‘(blank)’.

**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.
  
![Handling Null](https://docs.sisense.com/main/Resources/Images/Handling Null 2.png)

**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)
  
![Handling Null](https://docs.sisense.com/main/Resources/Images/Handling Null 3.png)

**Date Filters (Fields/Dimensions)**: Set a Time Frame, Calendar, or Ranking condition, or exclude
'N\A' using the List filter
  
![Handling Null](https://docs.sisense.com/main/Resources/Images/Handling Null 4.png)

**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)
  
![Handling Null](https://docs.sisense.com/main/Resources/Images/Handling Null 5.png)

#### 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