How can we help?

New post
0

Blank values are not counted correctly?

Avatar
Eka Ponkratova
None
Follow

Hi All,

There are three values in the column: Yes, No and Blank. I was using the below formula to find a percent of customers who didn't have a mobile marked as 'No' over Total. Interestingly the denominator was incorrect, and when I tried to see what category is responsible, I found that the count of blank was incorrect. I changed it to COUNTBLANK but it still missed like 1,000 values. Do you have an idea why is happening here?

COUNT(SELECT(SLICE(9b0055d3900697355d07ebc351636dde@C:C;), 9b0055d3900697355d07ebc351636dde@AT:AT;="NO"))/COUNTIF(SLICE(NOT(9b0055d3900697355d07ebc351636dde@AT:AT;="NO")))

Many thanks,

Eka

Date Votes

7 comments

  • 0
    Avatar
    Eka Ponkratova

    The problem still remains although I think I spent too much here today. I should have divided by total count. But the question remains, why is the blank count incorrect?

  • 0
    Avatar
    Meggan King

    Hi Eka,

    It's hard to say without looking at your data, but sometimes what we see in spreadsheets are blanks at the bottom, after the data. These are not detected if there is nothing in them. They are actually null values and ignored by the system. The best way to tell if this is happening is to evaluate the column of data. Does it return as many lines as you expect to see? If not, then we would suggest you pad out the bottom of the column of data with blanks, to make sure you are using the same number of cells across your columns in your calculations.

    If you are familiar with our modeler feature, you can model your data source and the system will handle this alignment for you. 

     

    If you'd like us to have a look, we're happy to help. Just send an email to support@klipfolio.com and we can have a look in your account. 

     

    Thanks

    Meggan

  • 0
    Avatar
    Eka Ponkratova

    Hi Meggan,

    Thank you for the response! Funny if you count total # of values, it is correct, but then, if you count Yes, No and Blank, you get less than the total count.

  • 0
    Avatar
    Meggan King

    Hi Eka

    That tells me that what looks like a blank is probably being treated differently behind the scenes. This is not unusual and can happen depending on what your data is like. Happy to have a look and advise why this is, if you'd like me to look!

    - Meggan

  • 0
    Avatar
    Eka Ponkratova

    Hm, below is the screenshot from querying the db. Is it a system bug that it treats null differently?

  • 0
    Avatar
    Meggan King

    Hi Eka,

    There isn't a bug, but COUNTBLANKS may ignore that and not treat it as a blank. 

    It's best for us to help with your actual data, so I'd suggest a ticket! support@klipfolio.com

    Thanks!

    - Meggan

  • 0
    Avatar
    Eka Ponkratova

    OK, thank you!

Please sign in to leave a comment.