[Looker Studio] How to Use the IFNULL Function and Practical Examples | Calculated Fields

May 7, 2024

[Looker Studio] How to Use the IFNULL Function and Practical Examples | Calculated Fields

In this article, we will clearly explain how to use the IFNULL function in Looker Studio and provide concrete examples of its application. Utilize the functions available in Looker Studio to process and visualize your data.

The IFNULL function is a function that can change how missing values in data are displayed or replace them with another number. We will also discuss alternative methods available in Looker Studio for changing the display of values.

What are Calculated Fields in Looker Studio?

Calculated Fields in Looker Studio are a handy feature that allows you to create custom fields by using operators (such as addition, subtraction, multiplication, division), functions, and regular expressions based on existing items for use in reports.

Also, for basic usage of Looker Studio, please refer to the "How to Use Looker Studio" guide. Looker Studio is a very convenient tool that is free to use and allows for the creation of easy-to-understand reports by connecting to various data sources, so let's make active use of it.

What is the IFNULL Function?

The IFNULL function is a function that allows you to set an alternative value if the value of a specified field is null (empty). Null is also referred to as a missing value. In data analysis and visualization, this function is useful for filling in missing data and for clearly presenting analysis results.

Syntax

The syntax of the IFNULL function is as follows:

IFNULL("field with missing values", "replacement value")

For "field with missing values", specify a field that contains missing text or numeric values. For "replacement value", specify an alternative value to set when there is a missing value.

Cautions for Using the IFNULL Function

There are important points to consider when using the IFNULL function:

Ensure Uniformity of the Result Type

Although you can specify either strings or numbers in the result, you must maintain uniformity within the same IFNULL function. This means that if the field with missing values is text, the replacement value must also be text.

How to Use the IFNULL Function

To use the IFNULL function, you need to create a calculated field in Looker Studio. There are two types of calculated fields: data source calculated fields and chart-specific calculated fields. This article will focus on creating a data source calculated field, but for more detailed differences between the two, please see the differences between data source calculated fields and chart-specific calculated fields.

To explain how to use the IFNULL function, we have prepared sample data consisting of refunds by store for each date. There are days when no refunds occur, which results in missing values.

Use case: Display missing values as 0 instead of null.

As shown in the sample data below, on dates and at stores where there are no refunds, the refund column will be left empty with no data.

(Quote:Google sheets)

When using this data as is to represent daily refunds in a Looker Studio table, the dates on which no refunds occurred will be displayed as null.

(Quote:Looker Studio)

We will use the IFNULL function to display this null as 0. First, create a calculated field as follows.

(Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula: /IFUNULL(refund,0)

In cases where there are missing values in the refund column, they will be changed to a value of 0 as instructed.

③ Save: Once you have completed entering the information, click save.

A new field called 'refund_ifnull' has been created as follows.

(Quote:Looker Studio)

We will use the field we created in the report. Then, the missing values marked as null are now displayed as 0.

(Quote:Looker Studio)

How to Change the Display of Null Without Using Calculated Fields

In Looker Studio, you can change the notation of null to any of the following four options without using calculated fields:

No Data
0
–
(Blank)

This time, we will set it to display as "-". First, select the relevant table in Looker Studio, then choose the Style tab and click the selection button for the "Missing Data" item.

(Quote:Looker Studio)

Next, select "Display as '-'" from the options. Then, "0" will be displayed as "-" as shown below.

(Quote:Looker Studio)

Using calculated fields offers more flexibility and options than this method, although it is simpler. However, settings must be applied individually to each table. If you want to use custom notation or deal with multiple tables, you should use calculated fields.