Fixing the looker studio no data result in data blends

Your charts are showing on looker studio “no data” after you’ve blended data, when one of the metrics reports 0 entries? Here’s the quick fix and an explanation for this behavior.

The problem: blends with calculations show no data when one value is 0.

Nothing is more frustrating than simple operations (such as adding hits from more metrics) returning the wrong output, or no output at all (such as the value in looker studio of “no data”).

Here’s how it typically happens

Calculate Field with SUM with data blend returning no data

We have a landing page where several different CTAs or button clicks open up a modal that is one step away from the conversion action. The client requested a simple funnel visualization in looker studio, so we’ve proceeded with creating two widgets with the specific event count, and an event filter to only show the wanted event (a specific CTA click). Then, we are summing those widgets results into a new widget that blends data from the two.

The report is much more complex, but you get the gist of it:

So, for the above time frame, all is looking good. The calculate field uses this formula:

SUM(metric1) + SUM(metric2)

But when we change the report time frame to a set of days when metric one or metric two didn’t collect any hits, there it comes the looker studio no data issue. When one of the two metrics returns no data, the whole addition results in no data, instead of showing metric a value (x) + metric b value (zero) = x.

Why does looker studio respond with no data?

This happens because one of the metrics is not actually responding with “0” as in a number state; it is indeed responding with a “null” entry, thus making the calculation impossibile due to a mismatch between numeric value and string value.

You can check out this blospost by Nimantha in Google looker studio reporting community to see the reference.

The solution: Using NARY_MAX

This is a nice workaround that works solidly. You ought to use the NARY_MAX function to output the maximum value of the highest argument. The calculated field would therefor look like this:

NARY_MAX(metric1, 0) + NARY_MAX(metric2, 0)

and therefor, fixing your report instantly.