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
Best 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!**2
Answers
-
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!**2
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 762 Beast Mode
- 65 App Studio
- 42 Variables
- 704 Automate
- 182 Apps
- 458 APIs & Domo Developer
- 53 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 133 Manage
- 130 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive