Displaying null values in sums

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

c1623c549828e2ef1bcd3b9c762d6ce6e34496b5.png

The SQL

bbef428a9b6355941351c049d431b33fb2843399.png

0 13 14.1K
13 REPLIES 13

kmahamk
Participant I

Hi…i see that Looker is not defaulting NULL values in Sum to 0. I still see the ‘Phi’ symbol. Is there any way that i can override this behavior so that i can display 0 instead of Phi?

Hi @kmahamk,

What does your query look like? For example, are you using pivots? Are you using type: sum or type: number and then doing the SUM() in the sql: parameter? Let me know and we can go from there!

Cheers,
Vincent

kmahamk
Participant I

Yes I’m using pivot on a column. Also I’m using type of count_distinct. Whenever there are no records for specific category in pivot column, i see that the count is displayed as phi symbol instead of zero. In my case I want the count to be displayed as 0 if there are no records for that pivot category instead displaying a phi symbol. Any way to display 0 in this scenario?

The way to do this would be COALESCE. This is a function that takes a list of values and returns the first non-null value in that list. So for example COALESCE(NULL, 0) returns 0.

This function is supported in table calculations - you can just write coalesce(${measure_name}, 0) as a calculation. This is the recommended solution.

Edit: removing the LookML “solution” because it does not work with pivots. Use the table calculation solution above.

kmahamk
Participant I

Thank you all for your help 🙂

Ipear3
Participant I

I have an additional question: How can we display a null value sum if we are using filters{}? filters is not supported for non-aggregate types.

Hmm that’s a good question. The easiest way would be to convert your filter to a CASE WHEN in the sql of the measure, and put it as a type: number like the main post suggests…

It’s possible that a better way exists though— Anybody have an idea?

Ipear3
Participant I

Coming back with a few more months of experience to solve my own problem…

Problem: Aggregate measure types automatically coalesce null values with zero. Aggregate measure types are the only types that support the filters parameter. Therefore, if there is a difference between 0 an null for your measure, and you’d like to use the filters parameter to limit drill results, you had been out of luck.

Solution:

  1. Use measure type: number with SUM in the sql parameter.
  2. Use the link parameter to create a drill with dynamic filters.

For example:
measure: weekly_losses
type: number
sql: SUM( CASE WHEN ${iswon}=“FALSE” AND ${week}=“Complete” THEN 1
WHEN ${iswon}=“TRUE” AND ${week}=“Complete” THEN 0
ELSE NULL END)
link: {label: “Custom Drill” url: “/explore/model_name/explore_name?fields=view.id,view.date,view.quantity&f[view.week]={{view.week}}&f[view.iswon]=“FALSE””}
}

Note: using a value variable {{ view.iswon }} caused the view (pre-drill) to fan out as if iswon was added as a dimension. I opted to write FALSE for weekly_losses and TRUE for weekly_wins and the view was as desired.

Hope this helps someone else!

For anyone who comes across this while googling like I did, I want to share another approach that my team uses for these:

  1. Create a regular measure of type: sum but keep it hidden
  2. Create another measure (called for example has_value) of type number for whether there is a non-null value in the field. You can do this with sql: max((your_dimension is not null)::int) in Redshift
  3. Finally, create your sum measures that you want to expose with type: number and sql: ${your_sum_measure} / nullif(${has_value}, 0).

The advantages of this are:

  • Your sum will display as null for any groupings where all values are null
  • It maintains any symmetric aggregates, as opposed to just doing a `type: number` and handling the SUM yourself in the SQL.

@michael_zearn i tried to understand your solution, could you please answer the below ?

 

For anyone who comes across this while googling like I did, I want to share another approach that my team uses for these:

  1. Create a regular measure of type: sum but keep it hidden
  2. Create another measure (called for example has_value) of type number for whether there is a non-null value in the field. You can do this with sql: max((your_dimension is not null)::int) in Redshift
  3. Finally, create your sum measures that you want to expose with type: number and sql: ${your_sum_measure} / nullif(${has_value}, 0).

The advantages of this are:

  • Your sum will display as null for any groupings where all values are null
  • It maintains any symmetric aggregates, as opposed to just doing a `type: number` and handling the SUM yourself in the SQL.
  • How did the Step 1 utilized in your logic? what did you do with the regular sum measure if it is hidden?
  • Step 3, i am trying to understand >» how is this step helpful? can’t we simlply sum Step 2? 


 

@sunnygud answers below:

  • The measure from Step 1 is “${your_sum_measure}” referenced in step 3.
  • Step 2 is just a 1 or 0. You need Step 3 to put it all together (sum the field you want to sum, but make it null instead of 0 if all values are null).

For people who aren’t into sql but still need to get the job done like me-

In my use case, I had to return 0 for null values with a pivot of dates. I used a table calculation and entered the following.

if(is_null(${count_outside}),0,${count_outside})

I am Getting null value when calculating column total, how can I solve this to give me total value ?

Top Labels in this Space
Top Solution Authors