Table to display number of null values

Victoria_G1
Victoria_G1 Member
in Cards, Dashboards, Stories

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

    Circle.png

    John Le

    You're only one dashboard away.

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

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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

  • Victoria_G1
    Victoria_G1 Member

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

    image.png

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

    Circle.png

    John Le

    You're only one dashboard away.

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

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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.

Categories