9 Replies Latest reply on Dec 19, 2013 8:00 AM by Andrea Miller

    Filtering for values not zero

    Chris Bundy

      New Tableau user so this may be basic.  How do I filter a column so that I see all values except zero?  Example, I have a baseline, a scenario and I take the difference between the two and only want to see non-zero values.

        • 1. Filtering for values not zero
          Andy Piper

          Chris,

           

          Right click on the pill within the columns shelf and choose Filter. If the field is a dimension, choose "Select from List" within the General tab, select 0 and then select the Exclude option in the bottom right. If the field is a measure, when you choose filter you could specify an At Least option greater than 0.

           

          Hope this helps,

           

          Andy

          1 of 1 people found this helpful
          • 2. Filtering for values not zero
            Chris Bundy

            Thanks Andy. That didn't seem to work.  The column is a "Quick Table Calculation" "Difference".  It contains both positive & negative numbers along with zeros.  When filtering, it gives the options of "At Least" & "At Most" so I can't filter out a zero.  Thanks.

            • 3. Re: Filtering for values not zero
              Andy Piper

              Chris,

               

              The easiest way is if you can go with the option of At Least and choose .001 or another number greater than 0.

               

              If not, you might try the following:

               

              • Hold down the CTRL key and drag the "Quick Table Calculation|Difference" field to the Filters shelf -- this duplicates the field within the filters.
              • Right click on this new filters field and choose Edit Table Calculation.
              • Click the Customize button.
              • The formula likely looks like the following:  ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), FIRST())
                • In case yours is different, I will use the <FORMULA> moniker in my expression below to indicate that you should use whatever formula you are using in your table calculation
              • Change the formula such that it looks like the following:
                • IF (<FORMULA>=0) then NULL else <FORMULA> END
              • Click OK to get out of the formula creation window
              • Click OK to get out of the Table Calculation window
              • You should be prompted with a Filter window and a range of values -- MAKE SURE "Include null values" IS NOT CHECKED and click OK.

               

              With the formula, essentially you are setting all zeroes within the table to nulls. Once the zeroes are in a null status, the option to not include them from the table exists.

               

              Hope this helps,

               

              Andy

              1 of 1 people found this helpful
              • 4. Re: Filtering for values not zero
                Tracy Rodgers

                Hi Chris,

                 

                Are you able to post the workbook or sample workbook (twbx file) so we can take a closer look?

                 

                -Tracy

                • 5. Re: Filtering for values not zero
                  Chris Bundy

                  Thanks Andy, that works (when I am only displaying the "Quick Table Calculation - Differences" field.

                   

                  However, I could not get it to work in a table in which I am showing both the original values and their differences (using the "Quick Table Calculation - Differences" field.  It will not let me pull that field into the filter shelf.

                   

                  So, what I am really looking for is way to show:

                   

                  "ColumnA"     "ColumnB"      "Difference" and then filter "Difference" to exclude zeros.  I run into this alot.

                   

                  Thanks

                  • 6. Re: Filtering for values not zero
                    Andy Piper

                    Chris,

                     

                    Instead of making a copy of the pill and placing on the filters list, apply the same formula above directly to the proper pill on the columns shelf. This way your other value will remain.

                     

                    Again the formula was:  IF (<FORMULA>=0) then NULL else <FORMULA> END

                     

                    Andy

                    1 of 1 people found this helpful
                    • 7. Re: Filtering for values not zero
                      Andy Piper

                      Chris,

                       

                      Did this solve your issue?

                       

                      Andy

                      1 of 1 people found this helpful
                      • 8. Re: Filtering for values not zero
                        Chris Bundy

                        Thanks for all of your help Andy.  It gets me most of the way there.  I'm not quite where I want to be with it yet and time permitting I'll repost later.

                         

                        Thanks again.

                        • 9. Re: Filtering for values not zero
                          Andrea Miller

                          I am a pretty new user and have to do things VERY simply.  I needed to filter out the 0's from a calculated field "Difference" - i am only interested in values that are not 0.  I created a new calculated field - "Filter Zero" that returns 1/0 if Difference is a 0 or not 0 - Seems to work pretty well - just requires additional fields.  I don't know if that will help.