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.
