Knowledge Drop
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.
LinkedIn
Twitter
