6 Replies Latest reply on Apr 20, 2018 11:45 AM by Nathan Schwinnen

    Counting Non-Null Values

    Nathan Schwinnen

      I used the ISNULL function to count null values in a certain field.  For the most part, this works very well.  I have a chart that displays the number of null values and the number of non-null values.  The one issue is that the ISNULL function displays "True" if the value is null.  For my purposes, I really want ISNULL to show "False".

       

      Basically, my table shows "X Is Complete" and so "True" Should mean it is complete.  Yes, I could easily change my table to read "X is not complete" and leave things the way they are, but I think that would cause more confusion.

        • 1. Re: Counting Non-Null Values
          Simon Runc

          hi Nathan,

           

          Could you just add a NOT(...) to your logic statement to reverse it? Or you can (if it's not an aggregate) change the Alias of True to False (I'd go for option 1...thinking of someone who may need to amend your model in the future, where this kind of thing can be hard to unpick when using Alias')

           

          hope that does the trick

          • 2. Re: Counting Non-Null Values
            Nathan Schwinnen

            Thanks.  Option 1 works perfectly. 

            After posting, I found the NOT function, but wasn't really sure where in the equation the NOT would go.  Turns out right at the beginning is perfect.

            • 3. Re: Counting Non-Null Values
              Simon Runc

              Cool!

               

              I always find the Calculation Syntax help is really useful. In the Calculation Window click on the little arrow to the right to show and hide it, and then you can search for the function you want by name/type. In fact if you have a function in your formula and click in the function name it will bring up the syntax help to the right too.

               

               

              Of course you need to know the function is there and what it's called, but Tableau uses most commonly known names for functions (things you find in Excel, SQL..)

              • 4. Re: Counting Non-Null Values
                Nathan Schwinnen

                The issue for me, because I am more familiar with Excel, is finding the subtle differences.  When I did this exact same formula in Excel, it was:

                 

                IF(C2<>"", "TRUE", "FALSE")

                 

                But that did not work in Tableau, at least not for the field I am using, which is a DATETIME field.  But, I always know there is a way to do it in Tableau.  It's just figuring it out.

                • 5. Re: Counting Non-Null Values
                  Simon Runc

                  Yes it will take a bit of time to adjust. Tableau is like a database, which means it requires datatype consistency...so you can't compare a DATE to a STRING, like you can in Excel (this is part of the reason Tableau is happy with 10s of millions of rows of data, and Excel struggles with a few hundred thousand).

                   

                  In your example, you are actually returning the string (word) TRUE and FALSE...and not the Boolean version (just for geeky interest Boolean TRUE/FALSE can be stored in 1 byte, but the STRING "TRUE" takes more space!). If you wanted to return the Boolean version in Excel you'd just need C2<>"" (so no need for the IF part). And like Tableau, you can wrap this in a NOT to reverse it NOT(C2<>"" )

                   

                  IMHO it well worth the work to make the switch. Excel kept me in Food and Shelter for the best part of 10 years, so I do have a soft-spot for her...but since transferring to think in database terms, I've never looked back

                  • 6. Re: Counting Non-Null Values
                    Nathan Schwinnen

                    No doubt.  I love Tableau.  I started using it two jobs ago and brought it to the next two companies.  I still have plenty of use for Excel, even sometimes as a data source for Tableau. 

                     

                    In fact, in the past, I have always used Excel as my data source.  Now I am connecting directly to the backend database on our servers. This is much better, but in the past if I couldn't figure out a formula in Tableau, I could just set up a macro in Excel to do the formula.  So even though I have used Tableau for years, I find myself needing to learn some new skills.