How to make NULL (∅) values zero within a dynamic field.

[liamsebestyen]

Hello, I am new to looker. This code is for a tile in my dashboard and I want it to show '0', when there are no results rather than NULL (∅). I have tried turning the measure into a custom measure, but it would not filter correctly like I needed. I also tried to move the dynamic field definition into my view file just as a measure, but I found it produced a different value when there is actual data. I also tried changing conditional_formatting_include_nulls: true , but I also found it did not work. I am new to looker so I may have done some of the above incorrectly. Thank you for the help !

title: Average
name: redundant_name
model: my_model
explore: appointments
type: single_value
fields: [average_of_age]
limit: 500
column_limit: 50
dynamic_fields:
- category: measure
expression: ''
label: Average Age
value_format:
value_format_name: decimal_0
based_on: patients.age
_kind_hint: measure
measure: average_of_age
type: average
_type_hint: number
filters:
appointments.is_missed: 'No'
appointments.is_cancelled: 'No'
appointments.is_pending: 'No'
patients.is_patient_active: 'Yes'
custom_color_enabled: true
show_single_value_title: true
show_comparison: false
comparison_type: value
comparison_reverse_colors: false
show_comparison_label: true
enable_conditional_formatting: false
conditional_formatting_include_totals: false
conditional_formatting_include_nulls: false
defaults_version: 1
listen:
Date Filter: appointments.start_date
Account ID: accounts.id
Entry Date: appointments.start_date
row: 15
col: 8
width: 8
height: 4

[salehaxsid]

Have you tried implementing this requirement in LookML when you create dimension with liquid parameter's "if- else" block in looker.

[liamsebestyen]

I have not tried to implement with an if-else block. I tried to use CASE WHEN logic inside a sql: block, but to no success. I did not try, because I believe this field needs to be in a dynamic field, and not the view. Can you let me know specifically what you know by creating a dimension with an "if-else" block, and where you would suggest to create the dimension.
Thanks a lot.

[salehaxsid]

The approach suggested by @Kevin_F_McCarth is perfect, the liquid parameter approach in backend is complex and it is not as optimized as this one.

[Kevin_F_McCarth · Accepted solution]

Hello liamsebestyen,

Working with these nulls is tricky.  Let me explain what I believe you are running into.

Generally speaking, Looker DOES tend to show null/missing measure values as 0s.  For example, the standard sql of type:sum measures includes a coalesce function to present the sum as 0.

However, in scenarios where there are literally no records in the underlying dataset (but there is nevertheless space for them on the table), a null value is presented.  Furthermore, since there's literally no data, it's not possible to update the presentation (e.g. in HTML parameter).  

Here's an example to hopefully clarify: This dimension is a case based dimension and so when i let the default dimension_fill feature be used on the dimension, I get a placeholder row for <10 (for which there is no underlying records at all).  On the right measure, notice there are 0s where there IS data (records in that dimension), but none of those records actually qualify to be counted in the measure... Again, this example is to highlight that looker is present null sign when there are literally no records in the underlying data

These null value cells commonly occurs in two different scenarios:

• when using dimension fill as shown above
• or when using pivots (where some cells represent a combination of dimensions for which there are no records

Unfortunately there is no known practical approach to change how these filled-in cells (i'll call them) are displayed in Looker, though removing filled-in values option may reduce the visibility of these on your dashboards.

I hope this helps you understand the nature of this issue and helps you work around it.  Best of luck.

[liamsebestyen]

Hello Kevin, thank you for your in depth response. You mention that sum: returns zero using COALESCE as built into Looker. I believe in this case the type: avg measure returns null when there is no data (0/0) division, and doesn't change this to zero. I have done some work arounds by doing the avg manually by doing COALESCE(AVG(${age},0) but I found it didn't work with the filters it needs. Let me know your thoughts.

[salehaxsid]

Great!! I didnt think of this approach and tried to manage null values in LookML.
Thanks for this valuable information.

[Kevin_F_McCarth]

Glad you found that approach for average.  If it works in some cases but not on other places (e.g. depending on filter as you mentioned), I'm guessing this is still a case like I described above where we have cells where there's actually no data at all and looker is 'filling' the missing cells (for fill_fields or pivot intersections).  Can you check if that is the case, as that is a special type of challenge that can't really be addressed in Lookml.

[liamsebestyen]

Hello Kevin. I managed to make it work using a table calculation. I appreciate all the help, in addition to providing more information on how looker works. I appreciate the effort you have put in to help me.