Question

When building a report I noticed some data is missing. How can I include 0 values in Explore reports?

Answer

There are a few ways to include 0 values in reports. The most popular are listed below. Use the one that suits your report configuration.

Note: If all cells in a column have NULL values, the column by default, will not show. The sample workarounds below only work for cells and columns that has zero as their value.

Time-based reports

If a report is time-based and there is no data displayed for NULL values of time attribute dimensions, like dates, weeks, or months, you can use the Time axis result manipulation.

Note: This feature is only visible if you are using a time-based attribute and add it to the Columns panel. The Time axis feature works only with a date field and will not work with 'Days of Week'. This manipulation only fills in (NULL value) dates that exist in between the earliest non-NULL value date and the latest non-NULL value date available. It does not work for the first NULL value date nor for the last NULL value date and that time attribute will still be hidden.

To enable the Time axis

  1. In report builder, click Result Manipulation ().
  2. Select Time axis from the list.
  3. Click Use Time axis checkbox; and then click Apply.

Reports with attributes on columns and rows

When a report has attributes on both columns and rows it sometimes has null values. This is especially visible in tables because some of the cells are empty. Use the Result metric calculation to replace these empty cells with 0 values.

For example, your report looks at the number of tickets created by year and group. It has Tickets in metrics, Ticket created - Year in columns, Ticket group in rows, and some cells are empty.

To create the result metric calculation

  1. In report builder, click Result Manipulation ().
  2. Select Result metric calculation from the list, then click Add a new metric.
  3. Enter a name for your result metric calculation.
  4. In the Formula box, from the Insert menu select the metric you need. In this example, it is COUNT(Tickets), then add "+0" to the formula. Your final formula will be: 
     COUNT(Tickets)+0
  5. When you are finished, click Clear used metric checkbox and then +Add.

Powered by Zendesk