University > Looker Studio Course > Formulas > Understanding and Utilizing the NULLIF Function in Looker Studio for Comprehensive Data Analysis

Understanding and Utilizing the NULLIF Function in Looker Studio for Comprehensive Data Analysis

Google Data Studio FUNCTION Introduction: NULLIF

NULLIF, an impactful function embedded in Google Data Studio, permits a more comprehensive understanding of data by accurately managing select data points. This function analyses two expressions and returns null if both expressions are equal. Conversely, the initial expression is returned if the two expressions are not equal.

How the NULLIF function works

This function's premise is the comparison of input expression and expression_to_match, wherein the 'input_expression' symbolizes the expression which needs to be evaluated. A valid expression can be used as the 'input_expression'. The 'expression_to_match', on the other hand, is compared to 'input_expression'.

If both 'input_expression' and 'expression_to_match' are identical, NULLIF will return null. If they do not match, the 'input_expression' is returned. Here as well, 'expression_to_match' can be any valid expression.

NULLIF Function Syntax

To understand the application of NULLIF, let's take a look at its exact syntax:

ruby
NULLIF(input_expression, expression_to_match)

This function employs two parameters: 'input_expression' the expression to be evaluated and 'expression_to_match', the expression which is then compared to the 'input_expression'.

Examples of NULLIF function

To have a more practical understanding of the NULLIF function's utilities, let's consider a sales-based scenario. Suppose your company collects sales data where 'no discount' is represented as ‘0’. You want to calculate the average discount rate, excluding these 'no discount' data points. The NULLIF function facilitates this easily, without the usual complications of traditional data analysis methods.

Creating a new field with NULLIF

You can use the following syntax to create a 'New_Discount' field:

ruby
New_Discount: NULLIF(Discount, 0)

This command reads as: "If the Discount field is 0, return null, otherwise return Discount."

Now, you can calculate the average discount rate, ignoring orders with no discount:

ruby
Average_Discount: AVG(New_Discount)

This will give you the accurate average discount across all sales where a discount was applied.

Limits of the NULLIF function

Even though the NULLIF function is a powerful tool in many scenarios, there are some limitations of its use. One of them is NULLIF cannot handle NULL inputs and will produce unexpected results.

Tips on using NULLIF function

Always double-check if your data contains null values before using NULLIF and handle them appropriately.
While using this function in large datasets, consider potential impacts on processing time as the function needs to evaluate every given expression.

By using the NULLIF function with attention to its limitations and best practices, it can become an integral tool for your data analysis toolbox in Google Data Studio.

More function to use with Looker Studio

TOREGION

Exploring the TOREGION Function in Looker Studio: A Comprehensive Guide to Region Identification and Visualization

COALESCE

Looker Studio function : COALESCE

Understanding the IF Function in Looker Studio: A Comprehensive Guide to Customizing Reports and Enhancing Data-driven Decision-making

MIN

Mastering the MIN Function in Looker Studio: A Comprehensive Guide on Data Analysis and Visualization Tools

CURRENT_DATE

CURRENT_DATE : Looker Studio function

Become a Looker Studio expert
for free

Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.

Get more video lessons