Filling blank cells with 0
Hi Team,
I've recently started domo to build reports and I'm trying to fill 0 in below blank cells in one of my reports.
The thing is, in my dataset there's no data for August month for the highlighted rows. I believe that's why I see blank in Domo. Till here it's fine.
Now, I'm trying to display 0 in these blank cells.
Initially, I used the property under General to fill empty cells with 0 value. But, this just fills 0 in the columns where I'm doing calculation as well.
I believe before empty cells are filled with 0 value, empty cells are ignored during calculation phase and hence after that, domo fill all empty cells with value 0.
I tried below formulas for calculated_count, but this also gave me the same result:
case when `count` is null then 0 else SUM(`count`) end
ifnull(count, 0)
coalesce(sum(`count`), 0)
coalesce(length(`count`), 0)
But none of them worked to give 0 in empty cells.
I've gone through multiple posts regarding this.
For example: https://dojo.domo.com/main/discussion/55174/filter-to-show-blank-cells
this suggests to use a case statement to filter out blank cells.
I added a calculated field, with formula: CASE WHEN `count` IS NULL THEN 'Blank' ELSE 'Not Blank' END
After that, I added this field in filters section and when I try to see available options in newly created filter, I see only "Not Blank" coming. I was expecting two values: Blank and Not Blank
Not sure, if there's something that I'm missing out.
Let me know, if any other information needed from my side.
Comments
-
@akki Are your values actually null or just blank? These are two different things. In your case statement for your filter, try the following and see what happens:
case when 'Count' = '' then 'Blank' else 'Not Blank' end
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
0 -
@RobSomers I believe values are blank because for highlighted rows, data does not exist in August month in my dataset.
I tried using
case when `count` = '' then 'Blank' else 'Not Blank' end
Even then, I get only one option in Filter (Not Blank)
0 -
Do you actually have a row of data in your dataset for August? With a null or blank value? Or is the issue that there is no data at all for August?
0 -
No, there is no row of data in my dataset.
0 -
I've prepared a sample data. After importing this sample data, I'm creating a pivot table like this
Here, year_val and month_name are calculated fields.
year_val: YEAR(`date`)
month_name: MONTHNAME(`date`)
Just wanted to know, if I can populate something in blank cells of count column, before I can start any computation on count column.
0 -
Sorry for the typo. I meant, No there is no row of data in my dataset for August as you'll notice in the sample excel attached in my last comment.
0 -
@akki given the nature of pivot tables, you will see a row for outcomes 5 and 4 since they exist for the month of september, but to your point there is no data there.
even if you wrote
coalesce( sum(`value`), 0 )
you wouldn't see a value in August because there literally is no data there.
in order to show a value in August, you need a row in your dataset that includes outcome5 and August. once the data is there, coalesce... will work.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Okay thanks @jaeW_at_Onyx
I was thinking the same, that data needs to be present in dataset so that I can do any computation on it.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive