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.
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.
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'.
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.
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.
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.
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.
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
All your data on Looker Studio
Build Looker Studio dashboard easily with your marketing data from all platforms and track your essential KPIs! No-code integration.
Start free trial now!
Optimize your data analysis
Get free Looker Studio dashboard template among a large collection of +50 stunning template! Elevate your data visualization.
Get free templates!.png)
