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?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive