Open navigation

How to convert null values to zero in Looker Studio reports

Replacing a “null” value with a 0 makes it easier to use math functions in the Looker Studio (formerly Data Studio) reports you build with Supermetrics.


You can also convert values to cosmetic zero, if you want to change their appearance in your reports but don’t need to use them for functions.


Instructions

Use the NARY_MAX or IFNULL functions to convert null to 0. Follow these steps to create a new field for the function.

  1. Open your report in Looker Studio.
  2. Click any chart or table to open Supermetrics.
  3. Click the Metric field.
  4. Click Create field.
  5. Enter the NARY_MAX(<yourfieldname>,0) or IFNULL(<yourfieldname>,0) function in the Formula field.
  6. Click Apply.


Here’s a Google Ads example with the “Cost” metric.



Cosmetic zero

To convert a value to "0" for cosmetic reasons, you can use a Looker Studio setting to style the results to any value that you want to represent “no results”.


Open your report and click the element you’d like to adjust. Navigate to the Style tab and scroll down to Missing data. Then, choose your preferred value from the dropdown.


Troubleshooting

If any value in the math is null, it will turn the whole result null when you’re using metrics to create custom calculations for your reports. 


To solve this problem, you can create a new calculated field for each metric with the above formulas, and then use these fields instead to build your custom calculations. With the nulls as zeros, the math will be calculated correctly.


In some cases, especially with unique count metrics like Reach and Views, it might be that the report is trying to sum deduplicated values. If this is the case, follow these instructions to resolve the issue.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.