Display Omitted Empty Values in a Table

In a report, metrics are broken down by one or more attributes, with a displayed value for each metric-attribute intersection. If there is no intersection, then nothing is displayed. In some cases, this behavior can be confusing to viewers.  For example, in the report below, the number of tweets and retweets (metrics) are listed by day of the week. However, data is displayed only for Thursday through Sunday.

To clarify the report you can force the insertion of rows for the other days of the week.

Steps:

The basic trick is to execute a count of the values of the attribute used to slice the metric. In this case, the attribute is Day of Week.

  1. Click the What tab in the Report Editor.

  2. You must create a metric in the Advanced Metric Editor. Click advanced and then click Custom Metric.

  3. For the name of the metric, enter period (.), which creates a very narrow column.

  4. For the metric definition, enter the following:

    SELECT COUNT(Day of Week (Mon-Sun)) BY ALL OTHER

     

    • The above metric performs a count of the available values of the Day of Week (Mon-Sun) attribute for each of the listed days in the table, so each value in this column should be 1.
    • The BY ALL OTHER clause prevents other attributes from filtering the generated values, so you have a consistent set of returned values no matter how the report changes. 

  5. Click Add.

  6. The column no displays values for the days containing data in the report. 

  7. To display the other rows with empty (null) values, you must apply custom number formatting. In the Report Editor, click Show Configuration. Then, click Custom Number Formats.

  8. For the # of Tweets and # of Retweets metrics, insert the following custom formatting, which forces the display of null values as zeroes:

    #,##0;[=Null]0

  9. For the new metric, insert the following custom formatting, which hides the display of the count values:

    [color=F8F8F8][backgroundcolor=F8F8F8]#,##0

  10. Click Apply.

  11. Minimize the width of the COUNT metric column.

Your report should now display:

For more information, see Formatting Numbers in Insights and Reports.

Add Limits and Goals to a Report
Creating a Headline Report