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
There are 2 ways to convert “null” values to 0: a formula, or the NARY_MAX function.
Convert values with a formula
The formula CASE WHEN <yourfieldname> IS NULL THEN 0 ELSE <yourfieldname> END will convert “null” values.
Follow these instructions to create a field with a formula.
- Open your report in Looker Studio.
- Click any chart or table to open Supermetrics.
- Click the Metric field.
- Click Create field.
- Enter your new field’s information, with the formula above in the Formula field.
- Click Apply.
Here’s an example of what this looks like in the Facebook Ads “Comments count” field.
Convert values with the NARY_MAX function
The NARY_MAX function offers another way to convert these values.
To implement it, follow the steps above, but enter the function NARY_MAX(<yourfieldname>,0) instead of the conversion formula.
Here’s a Facebook 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.