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

June 11, 2024

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

In this article, we will explain how to use the NULLIF function in Looker Studio and provide specific examples of its application. By leveraging the functions available in Looker Studio, you can process and visualize your data effectively.

The NULLIF function is used to remove unnecessary data or ignore specific values during calculations. We will provide a detailed explanation with actual data examples.

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 NULLIF Function?

The NULLIF function returns null if the specified values are equal; otherwise, it returns the value itself. Null indicates that no data is stored, which is treated differently from the numerical value 0. The number 0 represents an existing value, whereas null represents the absence of any value.

This function is very useful for filtering out unnecessary data or excluding specific values during analysis. For example, it can be used to avoid division by zero or to exclude data with certain values from analysis.

Syntax

The syntax of the NULLIF function is as follows:

NULLIF("Field containing the value to be nullified", "Value to convert to null")

You should specify a string or numeric field for the "Field containing the value to be nullified". This will convert specific values within this field to null.
The "Value to convert to null" will be converted to null if it matches the specified value.

How to Use the NULLIF Function

To use the NULLIF 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.

I have prepared sample data for refunds at each store by date to illustrate the use of the NULLIF function. I will explain using this sample data. There are days when no refunds occur, and in such cases, the refund column contains a value of 0.

Use case:Displaying the Average Refund Amount per Transaction in Looker Studio

The sample data is structured as follows. The refund column contains a value of 0 for days when no refunds occurred.

(Quote)Google Sheets

When displaying this data directly in Looker Studio to show refunds and their averages for each store, it looks like this:

(Quote)Looker Studio

Here, we are displaying the total refunds and the average refund for each store. This average refund represents the average daily refund for each store since the refund column in the spreadsheet includes 0 values. Specifically, from the table above, we can see that Store A has an average daily refund of $666.67, and Store C has an average daily refund of $500.

While this metric itself is meaningful, it does not convey how many times refunds occurred per store or the average refund amount per transaction when refunds occurred. Therefore, we will process the data to make it possible to display this information. We will use the NULLIF function to transform the 0 values in the refund data into nulls.

First, create a calculated field as follows.

(Quote)Looker Studio

① Field Name: Please enter any field name.

② Calculation Formula: NULLIF(refund, 0)

This instructs to convert the value of the refund field to null if it is 0.

③ Save: Click save once you have completed the input.

A new field named "refund_nullif" has been created as shown below.

(Quote)Looker Studio

We will now use the created field in the report.

(Quote)Looker Studio

By using the NULLIF function, when no refunds occur, the value will be null. Therefore, by counting the number of refund values, you can determine the number of refunds. For how to count numbers, please refer to the usage and examples of the COUNT function.

Additionally, instead of counting, if you want to find the average, this will give you the average refund amount per instance. From this, we can see that Store A had 2 refunds with an average of $2,000 per refund, and Store C had 2 refunds with an average of $1,500 per refund.

By slightly manipulating numerical values as we did with the NULLIF function in this case, you can expand the scope of your analysis. Please try utilizing this method.