How to exclude BLANK values in histogram custom bins?

user000253
user000253 Member
in Charting

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 Coach
    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!**

Answers

  • GrantSmith
    GrantSmith Coach
    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!**

Categories