In this post I’m going to introduce the extremely useful but rarely referenced NARY_MAX function. This function within Google Data Studio was first introduced to me by Lee Hurst (@Helpfullee) in a Twitter discussion when I was struggling to blend data from two data sets. The issue I was encountering was due to the presence of NULL values in one of the data sets resulting in an incorrect sum result. The solution to this issue was to use the NARY_MAX function within the calculated fields which I had created.
What is NARY_MAX?
The NARY_MAX function returns the maximum value from a series of input arguments, for example daily website visits across a month. While this can also be achieved by the more commonly known and much simpler MAX function, it differs in the fact that it allows you to mix fields, calculations, and literals. It is this ability that enables it to be utilised to substitute NULL values for zero values, enabling calculations to be performed correctly.
A detailed overview of the NARY_MAX function can be found in Google’s Help Centre.
Example Usage
For this example I’m going to use the NARY_MAX function to sum multiple individual Google Analytics goals to output a total goals figure.
If all of your goals have been triggered within the selected time period then the following calculation will sum correctly:
Total Goals = Goal 1 (Completions) + Goal 2 (Completions) + Goal 3 (Completions)
However if one of more of your goals has not been triggered within the selected time period, then the total figure will be reported as 0. This is due to Google Data Studio substituting 0 values for NULL. While all numbers have a value (negative, 0 or positive), NULL is not a value and as a result cannot be used within calculations.
To convert NULL values to a numeric value of 0, the NARY_MAX function must wrap around each individual goal reference within our original sum formula:
NARY_MAX(Goal 1 (Completions),0) + NARY_MAX(Goal 2 (Completions),0) + NARY_MAX(Goal 3 (Completions),0)
Lastly it is important to remember that the NARY_MAX function must also be added to all subsequent formulas which require use of the total goals figure. For example to calculate a total CPL figure for all goals, the formula would resemble the following:
Cost / (NARY_MAX(Goal 1 (Completions),0) + NARY_MAX(Goal 2 (Completions),0) + NARY_MAX(Goal 3 (Completions),0))
Have you ever used the NARY_MAX function or found other valuable uses for it, let me know in comments below.
