Filling blank cells with 0

akki
akki Member
edited March 2023 in Datasets

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.

Tagged:

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

  • @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)


  • 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.

  • 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.


  • 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.

  • @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"
  • 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.