Question

Ideas for displaying NULL values as 0


Userlevel 5
Badge

It’s not uncommon to want to display ‘0’ instead of NULL. Does anyone have any good techniques for handling this?


I usually suggest using SQL’s COALESCE() function to swap a NULL out with a zero, but I’m wondering if people have any better ideas.


Here’s what I do:


    - measure: cancelled_count
type: count
drill_fields: detail
filters:
cancelled: Yes

- measure: not_cancelled_count
type: count
drill_fields: detail*
filters:
cancelled: No

- measure: percent_cancelled
type: number
decimals: 2
sql: 100.0 * COALESCE(${cancelled_count}/${count}, 0)

Yeah, I know that’s not a great example. I bet you have better ones!


20 replies

We use coalesce. I believe that is the most logical SQL equivalent (at least for Redshift)

Userlevel 4

The challenge can be that for ‘missing’ dates, for example, the coalesce still won’t solve the problem. In those cases, the current solution would be joining a result data-set into a list of dates, and then using the same coalesce function. This can be an effective way to ensure every date is listed in a table.

We have a few explores set up to join to a dimension table containing all the dates. It does solve this problem, but some of the queries can be created are very taxing on the database. This is mainly because the join to dim_date is done at the same time as all of the other joins in the query. It would be much better if there was a way to force looker to perform the normal SQL as an inner query (resulting in only one row per day (or maybe a few if we have pivots)), then do a join to d_dates as the last step to avoid a large Cartesian join.

Userlevel 4

Hi Ryan,


That is exactly our plan, and we are hoping to tackle the problem in Q1 to make things much easier. Will update this thread once our solution is available in production.


Colin

Ran into this issue today. Any updates on the official Looker solution?

I’d be interested in an official Looker solution as well - in the meantime will use cutler’s method.

Userlevel 4

This has leaked out a bit longer than we’d hoped, but still top of mind. End of Q2 / early Q3 looking most likely now. Apologies, and feel free to shoot me a note if folks are looking for more details or have comments/questions.

@colin


We’re also looking to do forecasting based on a set of pretty complex data (300+ lines of SQL, some denationalization, and sum/average distinct Looker symmetric aggregations) that have many filters. We produce a change log of revenue changes using this table, and use symmetric aggregates to deal with day-to-day changes.


In the SQL world, in order to do forecasting, we would make these all derived tables and right outer join a date table with some forecasted metrics in order to do month-to-date analysis vs. forecasts.


In Looker; we have to create a derived table for each metric/segment we’re forecasting against in order to join to the date table. This obviously is not ideal! A Looker solution would make forecasting and numbers vs plan much easier to do.

Userlevel 4

Completely understand, SQL is just not ideal for lots of reporting functionality. Hope to have a much more natural solution soon. As engineering resources begin roll off a large in-progress project, this is one of our top 2-3 near term priorities.

Is there any update to this as of yet? Missing dates are not allowing for totals because the measures are still identifying as null. Thanks!

Userlevel 2

Hi Travis,


Using coalesce to replace null with zeros is still the recommended strategy here. To address the missing date issue specifically, are you utilizing the dimension fill feature? When this is enabled, we may see nulls in our included measures corresponding to these filled in dates. The totals feature should still work here (it will count nulls as zeroes), although the totals column will also display 0’s as nulls. One possibility is to use the table calc coalesce function to replace nulls with zeroes within the explore, like this:



If you are seeing that totals are not working for you period when you have nulls in your measures, please visit us at help.looker.com with screenshots of the behavior you are seeing.


Thanks!


Quinn

This worked for my case. Just in case it wasn’t clear,

create a table calculation that looks like this:


coalesce(${myviewname.count},0)
Userlevel 1

Has there been an update on this? 
The coalesce method works great for table calculations but breaks down when pivoting measures.  

is there any update to this? this issue creates a lot of confusion for business users who are looking to see zeros… we cannot expect them all to use table calculations which are inefficient for the number of places we’d like to see this feature implemented. 

Userlevel 1

Is there any update on this? 

Just to add that if we do for example a count and the result is null (no results), we cannot create a calculated field for that with coalense so we keep getting null which indicates business users for an error 

Has there been an update on this? 
The coalesce method works great for table calculations but breaks down when pivoting measures.  

Hello Looker support, @colin any update on this? This exactly the issue which I’m experiencing. I’m using coalesce but still experiencing nulls in my pivot.

I want to return 0 when both calculation field and the tile output is null

is it possible? 

I have a report that count the number of orders per customer

I use the calculation field to average the number of orders per customer.

if the tile not returning any results I get null average and not 0 

Any update here? I would like to get rid of the null values in pivot tables without using table calculations ...

+1. It’s been 6 years!

If anyone’s interested, here’s what I did:

First, COALESCE null-values….Then, use that view dim within an href liquid variable

 dimension: id {
type: string
sql: ${TABLE}.id ;;
# We add this to fill null values, for html: href functionality.
sql: COALESCE(${TABLE}.id, 'No Available ID') ;;
}


dimension: uuid {
type: string
sql: ${TABLE}."uuid" ;;
# We ensure we have an HTML reference, if it only exists
html:
{%if view_name.id._value != "No Available ID" %}

<a href="mylink/to/the/website{{view_name.id}}/view"
target="_blank">
<u>{{value}}</u>
</a> ;;
{% else %}

{{value}}

{% endif %}
;;

 

Reply