Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How do I count only non- null values in a row in table calculation

  • 12 July 2022
  • 3 replies
  • 455 views
Susan Y

I have a row of data with some null values, I just want to count the times that the value is not null.  

Is there a way to do that easily without writing out a large string?

3 replies

A

I am having the same challenge is there any way to count NULLs without having to change them to zeros?

 

Want to avoid changing things to zero as that value has a special meaning for our business.

Shantell1201

Same issue. i would like to count the number of nulls in a row in order to classify the variable 

Daniel Jantrambun

To count not null values with positive numeric values:

SUM(IF(my_field >= 0, 1, 0)) 

or if you also have negative values:

SUM(IF(my_field >= 0, 1, 0))  + SUM(IF(my_field < 0, 1, 0)) 

With string values:

SUM(IF(LENGTH(my_field) >= 0, 1, 0))

 

To count null values:

COUNT(my_field) - SUM(IF(my_field >= 0, 0, 1)) - SUM(IF(my_field < 0, 1, 0)) 

Reply


Terms of Service