Filling blank cells with 0
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
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.
@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.” -Superman
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.
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
- 7.7K All Categories
- 3 Connect
- 919 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 38 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 19 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 27 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部