Understanding NULL handling in Sisense [Linux-Windows]

What Is a NULL Value?

A NULL value represents missing, unknown, or undefined data. In Sisense, properly handling NULLs is essential for accurate reporting and analysis.

Note: Sisense’s behavior around NULL values may differ between Linux, Windows, and may change across releases. Always verify with your version’s documentation and release notes.

Key Concepts of NULL Handling in Sisense

1. Aggregation of NULL Values

SUM(NULL) = No Results
When aggregating (e.g., with SUM), Sisense treats NULL as unknown—not zero. This is a critical distinction in data analysis.

Example:
[1, -1] → SUM = 0
[NULL, NULL] → SUM = NULL (“No Results” in some Sisense widgets)

SUM(NULL) + 1 = No Results (Unknown + Constant)
Adding a constant to a NULL (e.g., SUM(column_name) + 1) produces NULL (“No Results”).

• This reflects standard SQL behavior and is explicitly mentioned in Sisense’s documentation.
  
  

2. Handling NULL Values in Filters

Sisense may exclude or ignore NULL values when filters are applied—especially with older versions or certain widget types. This can cause inconsistencies when trying to display or filter for blank/“N/A” data.

• Example:
  If a filter is set for non-NULL values, NULLs may be omitted from visuals, potentially hiding data you expect to see.
  
  
• Troubleshooting:
  If you need to include/exclude NULLs in filter results, check your filter’s logic, test in different widgets, and review your platform’s current documentation.
  
  

3. Pivot Tables and Scripting for NULL Handling

Pivot Tables in Sisense can be customized via scripting to control how NULLs are displayed—for example, converting NULLs to 0 or a blank.

Sample Script:

if (row['column_name'] == null) {
    row['column_name'] = 0; // Replace NULL with 0
}

• This technique is widely used to improve readability and data interpretation.

4. SQL and NULL Values (Standard SQL)

Replace NULL with a chosen value (e.g., 0 or 'N/A')—prefer a custom column, not a custom table.

Custom Column (recommended)

Use one of these expressions when adding a Custom Column:

Option A — COALESCE (preferred)

COALESCE([column_name], 0)

Option B — CASE

CASE

  WHEN [column_name] IS NULL THEN 0

  ELSE [column_name]

END

This creates a new column that returns 0 instead of NULL.

Do not use SELECT ... FROM ... here—that would create a custom table, not a column.

Direct SQL (custom table or live query)

If you’re intentionally writing a query:

SELECT COALESCE(column_name, 0) AS column_name_no_nulls

FROM table_name;

Text fields

For text, replace with an empty string or label:

COALESCE([text_col], '')

-- or

COALESCE([text_col], 'N/A')

After adding a custom column in ElastiCube models, rebuild/publish so widgets can use it.

5. Behavior of NULLs in Queries (Advanced/Optimization)

The way NULLs are returned may vary if query optimizations (like the “WHERE optimizer”) are enabled.

• With optimizer ON, queries may return rows containing NULLs.
  
  
• With optimizer OFF, these rows may be omitted.
  
  

Explicitly check for NULLs in your query if needed:

SELECT * FROM table_name WHERE column_name IS NULL

Source: Sisense Community Article – Null Handling in Sisense

Best Practices for Handling NULLs in Sisense

1. Use COALESCE in SQL:
   Replace NULLs with a default value for easier aggregation and analysis.
   
   
2. Customize Pivot Table Displays:
   Use JavaScript scripting to show NULLs as zeros or blanks for clarity.
   
   
3. Test Filters Thoroughly:
   Always validate how your filters behave with NULLs in different widgets.
   
   
4. Explicit NULL Checks:
   Use IS NULL or IS NOT NULL in queries for precise control over data returned.

Troubleshooting: Common Issues with NULLs in Sisense

• Widgets show “No Results” unexpectedly:
  Check for NULLs in source data; consider using COALESCE or scripts.
  
  
• Filters don’t show N/A or blank values:
  Some widgets or older Sisense versions may ignore NULLs. Test in Pivot Table, Table, and Indicator widgets.
  
  
• Aggregate calculations appear incorrect:
  Review how NULLs are treated—SUM(NULL) is not zero!
  
  
• Behavior changed after upgrade:
  Review your Sisense release notes and documentation for any NULL-handling updates.

Glossary

• NULL: A value indicating “unknown” or “missing data.”
  
  
• COALESCE: A SQL function that returns the first non-NULL value in a list.
  
  
• Pivot Table: A Sisense widget that can be customized to handle NULLs via scripting.
  
  

References & Source Attribution

1. Sisense Official Documentation:
   Handling NULL Values in Sisense (Linux)
   
   
• Direct source for aggregation, arithmetic, and filtering behavior.
  
  
2. Sisense Community Article:
   Null Handling in Sisense (FAQ)
   
   
• Source for scripting, filters, optimizer notes, and user-reported behavior.
  
  
3. General SQL Best Practices:
   SQL COALESCE Function
   
   
• For SQL syntax and examples.

By understanding how Sisense handles NULL values and utilizing best practices, you can ensure your dashboards and reports display data accurately and consistently. This knowledge is especially crucial when performing aggregations, filtering, and working with Pivot Tables.

=======

Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.