Table to display number of null values

Hello,

I am trying to create a table to display the number of blank cells in some columns in analyzer. I calculated the blank cell count using beast modes. However, when I filter in analyzer to display the blank cells as a count aggregation, the count evidently decreases in all fields as the filter function is an intersection, rather than analyzes each field independently. Any advice on how can this be achieved independently?

Thanks!

Best Answers

  • DashboardDude
    DashboardDude Contributor
    Answer ✓

    Hi @Victoria_G1 ,

    so you'll need a new formula that says "Case when (formula you have above)='None' Then 1 else 0 end and then you can sum those 1's.

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • ST_-Superman-_
    Answer ✓

    @Victoria_G1

    From your first question, it sounds like you are trying to filter your dataset for null values. The issue here is that NULL does not equal anything (not even itself). If you want to filter for null values, I would create a beastmode:

    IFNULL(`ExtRR Market`, 'NULL') 
    

    You can then filter by this field for the value "NULL".


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • marcel_luthi
    marcel_luthi Coach
    edited December 2023 Answer ✓

    @Victoria_G1, just making sure we're following correctly. You already have a formula that tells you the number of blanks across multiple columns, and is working properly, the problem comes when you want to filter things out, that the count changes, is this right?

    The question I have is whether you expect the filter to actually limit the rows displayed and being used for the card, but not affecting the "summary" null count, if that is the case you could achieve this with the help of the FIXED function, so that it'll always run totals from the full dataset, regardless of the filters applied.

    On the other hand, if you want a filter you can use to limit to only rows that contain blank cells, then you'd need to create a beast mode, as filters in Domo are always added in a required way (AND logic) and you cannot define filter logic (sadly). Something like:

    CASE WHEN '[BLANK]' IN (IFNULL(`Col1`,'[BLANK]'),IFNULL(`Col2`,'[BLANK]'),…) THEN '[BLANK]' ELSE '[FULL]' END
    

    And filter on whether this new Beast Mode returns [BLANK] meaning that at least 1 of the Col# is null. Hope this helps.

Answers

  • In addition, given this formula, how do I count all the cells that have ''None' in them?

  • DashboardDude
    DashboardDude Contributor
    Answer ✓

    Hi @Victoria_G1 ,

    so you'll need a new formula that says "Case when (formula you have above)='None' Then 1 else 0 end and then you can sum those 1's.

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • ST_-Superman-_
    Answer ✓

    @Victoria_G1

    From your first question, it sounds like you are trying to filter your dataset for null values. The issue here is that NULL does not equal anything (not even itself). If you want to filter for null values, I would create a beastmode:

    IFNULL(`ExtRR Market`, 'NULL') 
    

    You can then filter by this field for the value "NULL".


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • marcel_luthi
    marcel_luthi Coach
    edited December 2023 Answer ✓

    @Victoria_G1, just making sure we're following correctly. You already have a formula that tells you the number of blanks across multiple columns, and is working properly, the problem comes when you want to filter things out, that the count changes, is this right?

    The question I have is whether you expect the filter to actually limit the rows displayed and being used for the card, but not affecting the "summary" null count, if that is the case you could achieve this with the help of the FIXED function, so that it'll always run totals from the full dataset, regardless of the filters applied.

    On the other hand, if you want a filter you can use to limit to only rows that contain blank cells, then you'd need to create a beast mode, as filters in Domo are always added in a required way (AND logic) and you cannot define filter logic (sadly). Something like:

    CASE WHEN '[BLANK]' IN (IFNULL(`Col1`,'[BLANK]'),IFNULL(`Col2`,'[BLANK]'),…) THEN '[BLANK]' ELSE '[FULL]' END
    

    And filter on whether this new Beast Mode returns [BLANK] meaning that at least 1 of the Col# is null. Hope this helps.