1 2 Previous Next 18 Replies Latest reply on Aug 28, 2018 2:14 AM by Dwi Handayani

    Without deleting records , Hiding Nulls in filter

    Sandeep Kotha

      Hi All,

       

      I have a question on hiding the "NULL" in filter without removing the data.

       

      I have a table where some of the records have nulls. In the same data, I have a field called "Segment" as shown below.

       

      Month    Segment

      Jan         Asia

      feb          Pacific

      March     Pacific

      April        Null

      May        Asia

       

       

      When I apply filter, it shows Asia,pacific and NULL.

       

      My Question:

       

      I want the records of NULLS to be included in the report but do not want to show "NULLS" in the filter.

       

      Is this possible in tableau?

       

      Could someone help me on this ?

       

      Best Wishes,

      Sandeep

        • 1. Re: Without deleting records , Hiding Nulls in filter
          pooja.gandhi

          This can be done fairly easily if you are using a single data source without blending.

           

           

          See this: Removing Null Values from Filters | Tableau Software

           

          Hope this helps!

          1 of 1 people found this helpful
          • 2. Re: Without deleting records , Hiding Nulls in filter
            Sandeep Kotha

            Hi Pooja,

             

            Thanks for your reply.The steps that you mentioned above do not work for my question. The records are getting deleted when nulls are removed in filter as I already checked one of those steps.

             

            Also, I saw: Removing Null values from filters based ob secondary data sources. In this, parameters cannot be refreshed automatically when the tableau data is refreshed, so not useful for my work Any ways, thanks for your effort.

             

            Best Wishes,

            Sandeep

            • 3. Re: Without deleting records , Hiding Nulls in filter
              Sandeep Kotha

              Hi All,

               

              Let me brief what I want and what I have done.

                 

              Table 1:
              ClaimMonthWeekSalesRegion
              JanWeek 123$Asia
              FebWeek 324$pacific
              MarWeek 123$pacific
              MarWeek 163$Asia
              JunWeek 429$Asia
              JunWeek 353$pacific
              JulWeek 526$pacific
              AugWeek 329$pacific
              SepWeek 423$Asia
              OctWeek 383$Asia
              NovWeek 323$pacific
              DecWeek 463$Asia

               

               

              Table 2:
              MonthPlan
              Jul2,566
              Aug4,197
              Sep3,182
              Oct2,869
              Nov4,276
              Dec2,585
              Jan3,952
              Feb7,692
              Mar3,980
              Apr3,086
              May2,990
              Jun4,134

               

              I have used Joins to combine the table1 and table 2 based on Month as unique.

               

              Final Table

              MonthClaimMonthWeekSalesRegionPlan
              JanJanWeek 123$Asia3952
              FebFebWeek 324$pacific7692
              MarMarWeek 123$pacific3980
              MarMarWeek 163$pacific3980
              AprNULLNULLNULLNULL3086
              MayNULLNULLNULLNULL2990
              JunJunWeek 429$Asia4134
              JunJunWeek 353$pacific4134
              JulJulWeek 526$pacific2566
              AugAugWeek 329$29$4197
              SepSepWeek 423$Asia3182
              OctOctWeek 383$Asia2869
              NovNovWeek 323$Asia4276
              DecDecWeek 463$Asia2585

               

               

               

              My report/output should be :

               

                 

              MonthSum(Week 3)Plan
              Jul3,582 2,566
              Aug4,138 4,197
              Sep3,892 3,182
              Oct3,525 2,869
              Nov1,376 4,276
              Dec634 2,585
              Jan2,852 3,952
              Feb172 7,692
              Mar0 3,980
              Apr0 3,086
              May0 2,990
              Jun0 4,134

               

               

              You can observe in my report that : All 12 Months data should have in the report. I am getting the above output but when I put Region as filter like below:

               

              Region

              Asia

              Pacific

              Null

               

              If I remove or Hide  NULL , then the records with Plan=3086 and Plan=2990(Highlighted in Final table) are getting deleted from the output.

               

              Hope I am clear with my explanation about my requirements. If there is any other way to describe my report then please provide the inputs. I would be glad if there is an answer for my question.

               

              Thank you in Advance!

               

              Best Wishes,

              Sandeep

              • 4. Re: Without deleting records , Hiding Nulls in filter
                Ivan Young

                Hi Sandeep,

                I don't quite understand Sum(Week 3) column in your report output but that doesn't much matter for the purpose of filtering.  I would consider using a parameter rather than a true filter.  Something like below should work but may need to be modified for real nulls rather than 'NULL'

                 

                Regards,

                Ivan

                 

                IF  [Region Parameter] = 'All' THEN 'Show'

                ELSEIF [Region] = [Region Parameter] OR [Region] = 'NULL' THEN 'Show'

                ELSE 'Hide' END

                 

                 

                 

                 

                2 of 2 people found this helpful
                • 5. Re: Without deleting records , Hiding Nulls in filter
                  Sandeep Kotha

                  Hi Ivan,

                   

                  What you have shown in Screenshots is the one which I want. But I did not understand the below code that you have shared.

                  IF  [Region Parameter] = 'All' THEN 'Show'

                  ELSEIF [Region] = [Region Parameter] OR [Region] = 'NULL' THEN 'Show'

                  ELSE 'Hide' END

                   

                  "Region" field has Asia,Pacific and Null.

                  Is "Region filter" a Parameter or Calculated field or a Set?

                   

                  Could you please tell me the steps to do so?

                   

                  Thank you very much for your effort.

                   

                  Best Wishes,

                  Sandeep

                  • 6. Re: Without deleting records , Hiding Nulls in filter
                    Ivan Young

                    Hi Sandeep,

                    I didn't have time for a thorough explanation yesterday so I'll walk you through the steps.  I've also attached a workbook which should help.  One thing to note is parameters do not allow multi select, you will need to select all regions or just one region at a time.  Region Filter is set up to always include records with NULL region.

                     

                    The Region Filter is a calculated field that interacts with a parameter which will return one of 2 values show or hide.

                     

                    Below are the steps to implement.

                     

                    1.  Create your Region Parameter parameter from the Region field.

                     

                     

                    2.  Add an All value to the parameter.

                     

                    3.  Create your Region Filter calculated field.

                     

                    IF  [Region Parameter] = 'All' THEN 'Show' -- If the Region Parameter value selected is 'All' then all records are returned.

                    ELSEIF [Region] = [Region Parameter] -- If the Region Parameter value selected is Asia then all records with Asia will be returned

                    OR [Region] = 'NULL' THEN 'Show' -- This part is not dependent on the region parameter.  All records with a null region will be returned, always

                    ELSE 'Hide' END -- If  the conditions above are not met then the records will not be returned in the view.

                     

                    4. Drag the Region Filter field to filters and select 'show'

                     

                    Let me know if you have any questions.

                     

                    Regards,

                    Ivan

                    1 of 1 people found this helpful
                    • 7. Re: Without deleting records , Hiding Nulls in filter
                      Sandeep Kotha

                      Hi Ivan,

                       

                      I really appreciate for your patience in providing the inputs on this.I will implement the steps that you have shown me and let you in case of any questions.

                       

                      Thank you very much for your help.

                       

                      Best Wishes,

                      Sandeep

                      • 8. Re: Without deleting records , Hiding Nulls in filter
                        Sandeep Kotha

                        Hi Ivan,

                         

                        I have implemented the same and it worked for me perfectly.Thank you so much for your help.I have few questions on this.

                         

                        1.) When the tableau data is refresh, the Region parameter will also get refreshed ,right?

                         

                        2.) I can implement for other fields as well in the similar way,right?

                         

                        3.) In my report, there are some tables for which records with NULL are required in the report but do not show in the filter. This problem got solved as said in the above.

                         

                        In my report, there are some charts for which records with NULL are not required in the report and do not show those nulls in the filter.Could you please provide the steps for this as well?

                         

                        Hence,I can put those two things in two different dashboards so that two different filters act differently and accordingly.

                         

                        Thank you very much for all your replies.:)

                         

                        Best Wishes,

                        Sandeep

                        • 9. Re: Without deleting records , Hiding Nulls in filter
                          Ivan Young

                          Hi Sandeep,

                          I'm glad it worked for you, the major limitation of filtering this way is you can't do multi-select easily.  Below are the answers to you questions.

                           

                          1.) When the tableau data is refresh, the Region parameter will also get refreshed ,right?

                               Unfortunately not.  You will need to update the parameter.  It's pretty easy to update or create a parameter from a field, but it can be a pain if you introduce new values frequently.

                           

                          2.) I can implement for other fields as well in the similar way,right?

                               Yes, this will work to filter any dimension.

                           

                          3. In my report, there are some charts for which records with NULL are not required in the report and do not show those nulls in the filter.Could you please provide the steps for this as well?

                               You would need to remove the following from the Region ParameterOR [Region] = 'NULL'

                           

                           

                          This modified version would exclude nulls.

                          IF  [Region Parameter] = 'All' THEN 'Show' -- If the Region Parameter value selected is 'All' then all records are returned.

                          ELSEIF [Region] = [Region Parameter] THEN 'Show'-- If the Region Parameter value selected is Asia then all records with Asia will be returned

                          ELSE 'Hide' END -- If  the conditions above are not met then the records will not be returned in the view.

                           

                          Hence,I can put those two things in two different dashboards so that two different filters act differently and accordingly.

                               You can create many calculated filter fields and have a single parameter act on them

                           

                          Let me know if you have any questions.


                          Regards,

                          Ivan

                          1 of 1 people found this helpful
                          • 10. Re: Without deleting records , Hiding Nulls in filter
                            Sandeep Kotha

                            Hi Ivan,

                             

                            Thanks for the reply. Point 3 is useful for me.

                             

                            For the Point 2:

                            if the parameter doesn't refresh when the data is refreshed then this will not be useful for my work.

                            The whole point of my question is to avoid nulls in filter but to keep those record sin the report.

                             

                            Firstly Why did i get the null records in my data? Let me brief this first, so that there might be some alternative for this.

                             

                            Since, I have used joins to combine the two tables(as said in the above thread), I have got the null records for the other fields except for the "claim month" field.

                             

                            My question is that:

                             

                            I have two tables as shown below(What I have shown is just a sample data, I have many records):

                            Table 1:

                            LOSSClaimMonthWeekSalesRegion
                            JanWeek 123$Asia
                            FebWeek 324$pacific
                            MarWeek 123$pacific
                            MarWeek 163$Asia
                            JunWeek 429$Asia
                            JunWeek 353$pacific
                            JulWeek 526$pacific
                            AugWeek 329$pacific
                            SepWeek 423$Asia
                            OctWeek 383$Asia
                            NovWeek 323$pacific
                            DecWeek 463$Asia

                             

                             

                            Table 2:
                            MonthFYPlan
                            Jul2,566
                            Aug4,197
                            Sep3,182
                            Oct2,869
                            Nov4,276
                            Dec2,585
                            Jan3,952
                            Feb7,692
                            Mar3,980
                            Apr3,086
                            May2,990
                            Jun4,134

                             

                            Table 2 columns are fixed for one year.Every year those "FYPlan" values will get changed.We will input this once in a year.

                             

                            I have to join "FY17Plan" column in table 1. I should get the output in such away that all months should show in tableau output physically with their corresponding "FYPlan" Values. but if you see in the below snapshot:

                             

                            Some months like April, May are missing in this report because some times only few months data will be available in the data.It is not necessary that all months should exist in "Loss claim Month" field.

                             

                            Even if it doesn't have all months data in "Loss claim Month" field.At any cost, I have to show the report for all the months with their corresponding "FYPlan" values.

                             

                             

                            In my output , some months are missing :

                             

                            I request you to provide some inputs to resolve this issue.Please do the needful. If you need any other information,please let me know.

                             

                            Thank you.

                             

                            Best Wishes,

                            Sandeep

                            • 11. Re: Without deleting records , Hiding Nulls in filter
                              Ivan Young

                              Hi Sandeep,

                              The method I described is the only one I'm aware of to filter in the way you want.  My only other suggestion would be to alias your nulls as 'Do not Uncheck' and include them in your filter.

                               

                              Good Luck,
                              Ivan

                              1 of 1 people found this helpful
                              • 12. Re: Without deleting records , Hiding Nulls in filter
                                Sandeep Kotha

                                Hi Ivan,

                                 

                                I think your suggestion works in this case,I believe. Could you please tell me how to make "nulls" as

                                "Do not Uncheck" ?

                                 

                                I appreciate your reply.

                                 

                                Best Wishes,

                                Sandeep

                                • 13. Re: Without deleting records , Hiding Nulls in filter
                                  Ivan Young

                                  Hi Sandeep,

                                  You can create a calculated field name Segment No Nulls, the formula would be IFNULL([Segment],'Do not Uncheck').  You would then use the this field as your filter.

                                   

                                  Regards,

                                  Ivan

                                  • 14. Re: Without deleting records , Hiding Nulls in filter
                                    Ivan Young

                                    Or alternately you can create an alias for the null

                                     

                                     

                                     

                                     

                                     

                                     

                                     

                                    1 of 1 people found this helpful
                                    1 2 Previous Next