Knowledge Drop

Why do Nulls Still Appear in My Measures After Wrapping the SQL: Parameter in Coalesce()?

  • 6 April 2021
  • 0 replies
  • 556 views

Userlevel 1

Example a measure of type: average (or Sum/Count/etc) is producing Nulls in an Explore. I want to display 0 instead of Null.
 

measure: my_measure {

type: average

sql: ${orders} ;;

}


A gut instinct may tell us to wrap the sql: parameter in a coalesce()function:

measure: my_measure {

type: average

sql: COALESCE(${orders},0) ;;

}


However, Nulls will still display for this measure in the results table.


Why?


The coalesce is occurring before the aggregation. The average will return Null for values where there are no rows, so the inner coalesce never has a chance to operate.


How do we show 0 instead of Null?


We'll need to create a new measure of type: number (which does not perform any aggregation) to wrap the measure in a coalesce():

 

measure: my_measure_coalesced {

type: number

sql: COALESCE(${my_measure},0) ;;

}

 

Now, the measure will display 0 in place of any Null that is returned!
 

But a pivot is forcing the nulls...

 

Then we won't be able to use the above approach, because the nulls are coming through to the results after the coalesce and the aggregation. To replace the nulls with a 0 or an empty space, we can coalesce the results in the data table with a table calculation, and then hide the original measure from the visualization.

 


0 replies

Be the first to reply!

Reply