Displaying null values in sums

[cutler]

Looker Coalesces by Default

By default, Looker coalesces sum measures with zero. This gives us a nice “0” display rather than a NULL; in most cases we don’t care about the distinction between “zero” and “no data”. If we’re talking about daily sales, for example, no data is essentially the same thing as no sales.

Sums with Null Values

Sometimes, however, we do care about the distinction. In cases like these, I like to create a numeric measure (type: number or type: int) and then do the SUM() in the sql parameter.

Take this for example

In the following LookML, we create a derived table with a null value. We also create two sum measures, one with no monkeying around (total_dogs) and one that uses the above technique (total_dogs_with_nulls). As you can see by the associated results and SQL, total_dogs_with_nulls shows up as we want it (with null values)!

The LookML

- connection: thelook

- explore: orders
- view: orders
  derived_table:
    sql: |
      select null as "dog" union select 1
    persist_for: 5 minutes
    indexes: [dog]
  
  fields:
  
  - dimension: dog
    type: number
    value_format: '$#,##0'
    sql: ${TABLE}.dog

  - measure: total_dogs
    type: average
    value_format: '$#,##0'
    sql: ${dog}
    
  - measure: total_dogs_with_nulls
    type: number
    value_format: '$#,##0'
    sql: sum(${dog})

The Results

The SQL