How to exclude BLANK values in histogram custom bins?

My beast mode calculation is the following:

CASE

WHEN CEILING(`Flow Velocity  (ft/s)`)<= 10 THEN '0-10'

WHEN CEILING(`Flow Velocity  (ft/s)`)<= 50 THEN '10-50'

WHEN CEILING(`Flow Velocity  (ft/s)`)<= 100 THEN '50-100'

WHEN CEILING(`Flow Velocity  (ft/s)`)<= 150 THEN '100-150'

ELSE '150+' 

END


I have realised that any BLANK values in that column are being bundled into 150+ bucket. I can't seem to filter out any BLANK values through the filter function on the card. Is there a way I can remove these BLANK values so they are not counted at this specific card level?


Thanks

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓

    Hi @user000253

    Are your values blank meaning an empty string '' or are they actually NULL meaning no data?

    To handle both cases you could add an additional clause to your case statement with a COALESCE function:

    CASE
    
    WHEN TRIM(COALESCE(`Flow Velocity (ft/s)`, '')) = '' THEN 'BLANK'
    
    WHEN CEILING(`Flow Velocity (ft/s)`)<= 10 THEN '0-10'
    
    WHEN CEILING(`Flow Velocity (ft/s)`)<= 50 THEN '10-50'
    
    WHEN CEILING(`Flow Velocity (ft/s)`)<= 100 THEN '50-100'
    
    WHEN CEILING(`Flow Velocity (ft/s)`)<= 150 THEN '100-150'
    
    ELSE '150+'
    
    END
    

    COALESCE says to return the first non-null value it finds. So in this case if it's an empty string ('') it will return that value and compare it to an empty string, if it's null it will use the empty string to compare against instead of null. There's an extra TRIM in there just in case we have a value of all spaces to remove spaces from the beginning and end of our string (it would make a value of ' ' = '')

    And then just filter out the BLANK value in your card.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**