13 Replies Latest reply on Nov 6, 2013 1:37 PM by Egor Ushakov

    Hide Null from Quick Filter

    Rossella Blatt Vital

      Hi,

       

      I would like to hide the Null value from a quick filter. I handle to hide it from the view, but I can't figure out how to not display it in the quick filter. For example if my data contains:

      Null

      A

      B

      C

      I would like the quick filter to display only:

      A

      B

      C

      The only solution I found so far is to use a set. Though I need to use a target filter in the filter action in the dashboard which works only with fields (not set).

       

      Any suggestion?

      Thanks in advance for your help

      Rossella

        • 1. Re: Hide Null from Quick Filter
          Rossella Blatt Vital

          I found a workaround:

          1) create a set that excludes Null

          2) create a calculated field that keeps only the true values of the set:

          IF [MySet]= TRUE

          THEN MyFieldValue

          END

          3) Display the quick filter of the calculated field and select "show only relevant values"

           

          Though any simpler way is very welcome! This still sounds too convoluted for such a simple thing...

           

          Thanks

          Rossella

          1 of 1 people found this helpful
          • 2. Re: Hide Null from Quick Filter
            Matt Lutton

            You can also filter the null values at the data source level, so they don't show up.  Similar to what you're already doing, you can simply duplicate the field and use either the duplicate or the original to filter out the null value, and use the other as your shown quick filter, set to "only relevant values".      

            • 3. Re: Hide Null from Quick Filter
              Rossella Blatt Vital

              Thanks Matthew! I can't change the data at the data source level, but the duplicate field is definitely a more direct way to filter the nulls out.

               

              Thanks!

              Rossella

              • 4. Re: Hide Null from Quick Filter
                Matt Lutton

                When you say you can't change the data at the data source level, I want to make sure you understand what I mean.  You don't have to actually have access to the data source or be an admin to do this--once the data is in Tableau, you can choose the "Data" menu, then choose your data source and select "Edit Data Source Filter".  If you want to hide all nulls by default, you can use this option, or use the duplicated field option at the data source filter level.

                1 of 1 people found this helpful
                • 5. Re: Hide Null from Quick Filter
                  Rossella Blatt Vital

                  Hi Matthew,

                   

                  thanks. This is a very nice feature I wasn't aware of. Though I don't think it will apply to my case because, if I understood correctly its functioning, it filters out all rows with the selected field = Null. In my data I am both joining multiple tables and using multiple sources, so I am expecting to have many records that, depending on the specific view will have nulls or not.

                  For example in the following table nothing should be filtered out at the data level, but when we display say letter and measure 1, then I would like to display the measure 1 quick filter without any nulls. If I understood correctly the "Edit Data Source Filter" will not work in this case.

                   

                  lettermeasure 1measure 2other field
                  A1 abc
                  B2 def
                  C3 ghi
                  D4 jkl
                  E5 lmn
                  F6 opq
                  G rst
                  a 10uvw
                  b 20xyz
                  c 30aaa
                  m bbb

                   

                   

                  Though it is a very nice feature and I am sure I will use it in the future.

                  Thanks

                  Rossella

                  • 6. Re: Hide Null from Quick Filter
                    Lindsay Reed

                    Hi Rossella - did you ever come up with a solution?  I'm having a similar problem where I want to exclude the null option from the quick filter, not filter out the null values.

                     

                    -Lindsay

                    • 7. Re: Hide Null from Quick Filter
                      Rossella Blatt Vital

                      Hi Lindsay,

                       

                      so far the calculated field on top of the set that excludes the nulls seems to be the most reliable solution I found so far.

                       

                      In specific cases I found some more straightforward solutions, but they will work only under specific circumstances. For example, if the dimension of the quick filter is null when one of the measures in your view is null, then you can filter out the nulls (of the dimension) by dropping the measure with the nulls to the filter shelf and selecting special>non null values. Then select "show only relevant values" on the quick filter of the dimension. For example:

                       

                      DIM1DIM2MEASURE1MEASURE2
                      aA110
                      bB220
                      cC330
                      dD440
                      e5
                      F60
                      gG770
                      hH880

                       

                      If you drop DIM1 to the columns shelf, MEASURE2 to the rows shelf and MEASURE1 to the color shelf, then you can drop MEASURE1 to the filter shelf, select special>non null values and then select show only relevant values in the quick filter of DIM1.

                       

                      Another solution is to create a worksheet with only the dimension you want to exclude the null for in the filter shelf. Here you exclude null and you select to apply this to all worksheets using this data source or to the specific worksheets where you plan to display the quick filter. In these worksheets you will need to select display only the relevant values from the quick filter. Though I have to admit this approach doesn't work all the times and I haven't been able yet to figure out why sometimes it works and sometimes it doesn't.. If I figure this out I will post it!

                       

                      Hope this helps.

                      Rossella

                      • 8. Re: Hide Null from Quick Filter
                        Egor Ushakov

                        Thanks, Matthew, for the "Edit Data Source Filters..." trick. But I can't get an idea what field duplication you're talking about. You mean duplicate fields on Filter shelf? But the only option I managed to do is "duplicating" Date field with different periods chosen as filtering condition (see screenshot). And any trying to place onto Filter shelf State field were unsuccessful.

                        screenshot.png

                        • 9. Re: Hide Null from Quick Filter
                          Matt Lutton

                          When referring to field duplication, I mean in the data window on the left side.  You can right click on a field, and duplicate it/create a copy.  Then, you can use both fields on the filter shelf--one as the shown quick filter on your dashboard, and another filter on the duplicated field to exclude null values. 

                          • 11. Re: Hide Null from Quick Filter
                            Egor Ushakov

                            Duplicating field trick is amazing! But I think it's worth to mention that one should set "Only Relevant Values" in Quick Filter settings. Am I right? Because with default "All Values In Database" I got only the following picture (I tried to remove 2008 year from Quick Filter):

                            Windows_8.1_Preview__English__-_Parallels_Desktop.png

                             

                             

                            UPD after skimming the text at link given above:

                            ... or using "Show Fewer Values"

                            • 12. Re: Hide Null from Quick Filter
                              Matt Lutton

                              Yes, you are absolutely right.  However, in cases where you can use a Data Source filter, that won't be necessary.  Cheers!

                              • 13. Re: Hide Null from Quick Filter
                                Egor Ushakov

                                Many thanks, Matthew, for pointing in right direction!