10 Replies Latest reply on Dec 25, 2017 2:46 PM by Yingying Zeng

    What is the filter drop down (All) 's logic Tableau Blending

    Yingying Zeng

      Dear Community ,

       

      When blending the data in tableau in one field, for example, State. We have the drop down option as (All)  in the filer.

      What is the second source (All) logic when blending?  Considering it is the tableau (All) , not something in the database, Does the blending still work accurately?

       

      Thanks a lot,

        • 1. Re: What is the filter drop down (All) 's logic Tableau Blending
          Joe Oppelt

          There is no value "All".

           

          "All" is a convenience supplied by Tableau to let you select everything without having to click all 100 choices in the filter.  Under the hood, Tableau just sees a whole bunch of different values that happens to constitute all of the available choices.

          1 of 1 people found this helpful
          • 2. Re: What is the filter drop down (All) 's logic Tableau Blending
            Yingying Zeng

            Hello Joe Thank you so much for the reply and I have two follow up questions there:

             

            1. How the Tableau 'All' Works with data blending and cascading filters?

             

            If we have a hierarchy filter -city, it is cascading to State and only show in the filter drop down when there is an  order in this city. For example, I assume the City values in the filter drop down are City A, City B, City D ( City C is not in drop down because there is no data for City C for this month) from the primary data source ( Internal data)

            When we use the tableau 'All' for City, is the number  the added up for (City A, City B, City D) in both internal and external data when blending by State and City?

             

             

            2. When the tableau (All) have duplication,how can I built a customized 'All' to remove duplication for the count ?

            The business case is to know how many orders we got  for different industries or all Industry in each Geography in each month.

            The cascading  filters we have the tableau dashboard is Geography & Customer Industry (related value only, I guess the cascading does not make a difference in this case )   Please see data sample and  the dashboard in the attachment.

             

                  

            Order IDOrder NumberCustmer NameCustomer IndustryGeographyMonth
            011AmazonRetailSeattle2017-10
            011AmazonITSeattle2017-10
            021MicrosoftRetailSeattle2017-10
            032AmazonRetailSeattle2017-10
            032AmazonITSeattle2017-10

             

             

            When we use the tableau 'All' in the drop down filter of Industry, The numbers of Orders for this month is duplicated since Amazon has been counted twice in IT or Retail Industry

            However, we can not remove either IT or Retail for Amazon since we want to see its number counted in IT or Retail when the industry get selected separately for IT or Retail.

             

            Fro example, When we choose IT, the Amazon 3 orders should be included while we choose Retail, the Amazon 3 orders should be included too.

             

            Is there any way remove the duplication in All industry in tableau and align it with the industry filter in this case ?

            • 3. Re: What is the filter drop down (All) 's logic Tableau Blending
              Joe Oppelt

              +Can you post a sample workbook demonstrating what you are seeing?

              • 4. Re: What is the filter drop down (All) 's logic Tableau Blending
                Yingying Zeng

                Hello Joe,

                 

                I have the sample workbook in the last post for the case two. Still thinking how to do the sample book for case one.

                Could you please help to look at the case two?  

                Thanks a lot

                • 5. Re: What is the filter drop down (All) 's logic Tableau Blending
                  Joe Oppelt

                  (V 10.2 here)

                   

                  Your data says there are two orders for Amazon/IT, and two more for Amazon/Retail for order ID 03.

                   

                  Tableau is doing what your data reflects.  If you shouldn't be adding up 2+2 for order #3, then you need to change the way your data is gathered.

                   

                   

                  Or just ignore the bad data in [Numbers of Orders] and go with [Number of Records].

                  • 6. Re: What is the filter drop down (All) 's logic Tableau Blending
                    Yingying Zeng

                    Hello Joe,

                     

                    sorry for the confusion.The data is not bad and we did it on purpose since we want to the amazon orders both falling in IT industry and retail industry. 

                    There are two orders for amazons and both of them get duplicated when we use the tableau ('All') since Amazon orders get added in IT and retail industry as "All' Industry

                     

                    In another word, we allows /wants one customer to multiply industries to get the industry counts but when it goes to all, numbers get duplicates because order ID 01 and 03 is unique for each industry(retail and IT) but duplicate when adding them together.

                     

                    I am sorry that it sounds very weird but that is the what the business wants

                    • 7. Re: What is the filter drop down (All) 's logic Tableau Blending
                      Joe Oppelt

                      I understand now.

                       

                      I can do something.  Give me some time.  I'll be back with you later.

                      • 8. Re: What is the filter drop down (All) 's logic Tableau Blending
                        Yingying Zeng

                        Thanks a million  Joe.  I tried to use LOD and calculated field but have challengings.

                        I am so thrilled that I can get your help there.

                        Thanks again!!!

                        • 9. Re: What is the filter drop down (All) 's logic Tableau Blending
                          Joe Oppelt

                          See attached.

                           

                          Basically you want to add up things one way when all values have been chosen, and add them up a different way when less than all values have been chosen.

                           

                          I created two calcs in the attached.  One ( [Industries on this sheet] ) tells you how many industries could possibly be chosen.  It uses a FIXED LOD, which gets evaluated before any filters have been applied to the sheet.  Because it is a FIXED LOD, I can make it a dimension, so I did.  (Not really important, but that's what I usually do.)


                          The other is an EXCLUDE LOD.  it does a COUNTD of what's on the sheet.  I EXCLUDE a bunch of dimensions (more than was really necessary, BTW) so that it does the COUNTD without regard to partitioning the data by dimensions.  (Thus, COUNTD for everything on the sheet.)  I added more because I tested it on Sheet 3 as well.  Usually I make a separate one of these per sheet (thus, the name of the calc:  [... on this sheet].)  this tells me how many the user has selected.

                           

                          Note that I used MIN when I put this on the sheet.  This calc is on every row, so if I did SUM() it would add up the value on all the rows and you would get a giant number.  I could have equally used MAX or AVG.

                           

                          Now you can compare these two calcs and do your data analysis the way you want to for each condition.  If they are equal, the user has selected ALL.  If not equal, the user selected something less.

                           

                          I displayed these calcs in the title of the sheet so you can see how they operate.

                           

                          I could have cut corners in your example by not creating the [...in this database] calc because as the example stands the value on [on this sheet] can only be 1 or 2.  But I suspect that you will have more industries than 2 in your full set of data so I went with the more complete solution here.

                          • 10. Re: What is the filter drop down (All) 's logic Tableau Blending
                            Yingying Zeng

                            Hello Joe,

                             

                            Sorry for the late reply and thank you so much for your help !!! It is great to know how to get the number of industries in the sheet and the number of industries in the database while I still do not know how to get the right number of "Number of Orders:

                             

                            I expect to have 4 orders when industry filters has been set to "All"  while the number of orders are 7 because company as Amazon has been mapped to both IT and Retail, which is what we want

                             

                            Amazon actually only place 3 orders( one from OrderId 01 and two from OrderID03) while the orders in retailer and IT from Amazon has been added up when industry filter is chosen as All, which is the duplication ( the numbers of order is right when choosing Industry is IT/ Retail)

                             

                            Could you please still help more to see how can I get the right number of orders when choose industry All

                             

                            Attached is the workbook I re-organized the breakdown2 and hope it can be a little bit clear.

                             

                            Thanks again and Merry Christmas!!

                             

                            Remove Duplication All.png