How to display 0 when there is No Data. It currently displays "No Data: There was no data found for the visual

Question & Answer
, ,
1

Is there a way to show 0 even if there is No Data ? I tried using IsNull and also tried the formatting option to set the Null Values as ‘0’ but nothing worked and it still displays No data in my dashboard.

2

Hi @Rus, handling cases where you want to display ‘0’ instead of ‘No Data’ can be challenging, especially if the data source itself does not contain records to display. Here are a few approaches you can try to manage and display zero values where data might be missing:

  1. Use Calculated Fields to Replace Nulls: Instead of using the built-in IsNull function, you can use a conditional calculated field to replace null values with zero. Here’s how you might do this:
ifelse(isNull(your_field), 0, your_field)

This formula checks if your_field is null, and if so, replaces it with 0. Otherwise, it uses the value of your_field.

  1. Ensure Your Data Model Accounts for All Categories/Dates: Sometimes, the issue stems from the dataset not having entries for certain categories or dates. Ensure your data model includes all possible categories or a continuous date range, even if some categories or dates do not have corresponding data points. This can often be handled in the data preparation or transformation stage (e.g., in your SQL queries or data pipelines).
  2. Use OUTER JOINs in SQL Queries: If you are preparing your dataset with SQL, consider using OUTER JOINs instead of INNER JOINs. This ensures that all records from the primary table are included, even if there’s no matching record in the joined table:
SELECT a.date, COALESCE(b.value, 0) as value
FROM date_table a
LEFT OUTER JOIN data_table b ON a.date = b.date

In this SQL, date_table should have a record for every date, ensuring no dates are missing in the final output.

  1. Handling Aggregations with No Data: If you are aggregating data and some categories have no data, you might see ‘No Data’ in visualizations. To address this, use the calculated field approach in your visualizations to convert nulls into zeros.
  2. Adjust Visualization Settings: Check the settings of your specific visualization type in QuickSight. Some visualizations might have additional settings to handle null values or to adjust how data is displayed when no values are present.
  3. Check Dataset Configuration: Sometimes, how the dataset is configured, including how fields are calculated and aggregated, can impact the display of zero values. Ensure your dataset’s configuration aligns with your reporting needs.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like
3

Hi @Rus - I believe this is default behavior and you can not change it.

Let’s hear from other experts, tagging @David_Wong @sagmukhe for their advise.

Regards - Sanjeeb

1 Like