9 Replies Latest reply on Mar 10, 2015 8:29 PM by Bruce Segal

    How to count non zero value

    Jie Hao

      I want to count any value which is not zero. I realized by default if I use CNT in tableau, it will still count for anything contained in my excel (incl. 0). And I want to get rid of the 0 empty cell.

       

      Can anyone help? Thanks.

        • 1. Re: How to count non zero value
          Tableau kumar

          try it once like the below syntax;

          if sum() <>0 then count[Customer_ID]) end

           

          On Tue, Mar 10, 2015 at 5:14 PM, Jie Hao <

          • 2. Re: How to count non zero value
            Jie Hao

            Hi,

            Thanks for your reply.

            I'm using the CNT functionality in stead of a calculation set. Please see the attached screenshot.

            So I was wondering if there is a way to count non zero within the CNT default measure.

             

            Tableau1.PNG

             

             

             

            • 3. Re: How to count non zero value
              Ramnath Kamakoti

              The count function does not count null values.An easy way to acheive your objective would be to create a field that returns one if your measure is not zero and null otherwise.Now use the count function on this calculated field to get your count

               

              Calculated field 1 : if measure>0 then 1 else null end

              Calculated field 2 : count(Calculated field 2)

              • 4. Re: How to count non zero value
                Jie Hao

                Hi Ramnath,

                 

                Thanks for your reply.

                 

                In fact, the count founction does count null or zero values. I have double checked with the data I have. Quite odd huh?

                And I don't tend to use any calculation field, just wanna keep it simple to plot the pie chart (see the attached graph).

                • 5. Re: How to count non zero value
                  Ramnath Kamakoti

                  Can you check if the field that you are using count for is not a dimension but a numerical measure as if it is a dimension tableau doesn't differentiate between zero and non zero values.

                  • 6. Re: How to count non zero value
                    Jie Hao

                    In my data set, it's all numerical based column. eg, 1000, 0, 200 etc.

                    And the CNT does also count for 0 in this case....

                    • 7. Re: How to count non zero value
                      Noah Salvaterra

                      Count should definitely include zeros in general, but it is possible that the way it handles nulls isn't consistent. If Nulls are being counted in your case, then I'm guessing it has something to do with your database setup or your particular connection type. In that case, how about creating an indicator calculation that would label non-zero values. i.e.

                       

                      if [YourMeasure]<>0 then 1 else 0 end

                       

                      Then you could get the count of non-zero values by summing this indicator.

                       

                      Incidentally, for anyone who hasn't noticed that Number of Records is a calculated field in Tableau, or why it shows up as a sum instead of a count when you drag it out onto a shelf, it is also an indicator. It just happens to always be 1, click in and see for yourself. I don't recommend changing it though, not unless it is April 1st anyway.

                       

                      N.

                      • 8. Re: How to count non zero value
                        Jie Hao

                        Hi everyone,

                         

                        Thank you all for your reply. I have just figured if I keep "blank" in my excel cell, Tableau will not count it. If I keep "0" in the cell, it will count. Just for others who have simliar issues.

                        1 of 1 people found this helpful
                        • 9. Re: How to count non zero value
                          Bruce Segal

                          Jie: I'm glad you figured that out. Tableau treats true blanks in Excel as NULL. If there's a space character in the cell, Tableau will treat it as not NULL.

                           

                          And if Tableau ever treats what looks like a true blank cell as if there's a space in it, you're not going crazy.

                           

                          I discovered there are times when using .xls files that there are what I call phantom blanks. They look like blanks in Excel, and they don't have a space in them, but Tableau sees them as having a space, and thus will count them instead of seeing them as a NULL. I've found this usually happens when a data tool sends me an .csv file and I save it as a .xls file. The way I've found to correct it is to copy and paste a blank cell from outside the columns where there is data on top of the cells with the phantom blanks.

                          1 of 1 people found this helpful