1 2 Previous Next 17 Replies Latest reply on Jun 10, 2015 7:08 AM by Mohamed Yahia

    Create a filter with 19 boolean dimensions COMBINED

    Prome nn

      Hi, maybe this's a newbie question!

      I've 19 boolean dimensions and I want that the user could select a subset of them for use as a filter. I can achieve that using 19  parameters but this solution is not user friendly at all!

      I would like to have something like this:

      Senza titolo.tiff

      Where A,B...N are my boolean dimensions. So the user clicking on B, E and G will filter my data where the dimensions are B=TRUE AND E=TRUE AND G=TRUE (the value of the other dimensions could be both True or false..I don't mind it).

      I've too many dimensions for use a parameter for every single dimension... so how this is achievable??

       

      THANKS!!!

        • 2. Re: Create a filter with 19 boolean dimensions COMBINED
          Chris McClellan

          It might be easier if you provided some sample data, but why use parameters when it sounds like Quick Filters will work for this solution ?

          • 3. Re: Create a filter with 19 boolean dimensions COMBINED
            Prome nn

            Thanks for the reply!!!

            My tableau example represent the profit for a set of hotels where my dimensions are a list of service which the hotel provides.

             

            This is an example of what I want to achieve. Note that ACCESSO_AI_DISABILITI, ..., ZONA_FIERA are my boolean dimensions (where "disponibile"=TRUE and "non disponibile"=FALSE (sorry for this, I'm italian ) ) . The GUADAGNO measure represent the profit of the hotels. So in the dashboard, if the user click on "Aeroporto" and "Mare" the GUADAGNO measure will be filter by all the hotels where "Aeroporto = TRUE AND Mare=TRUE" (the value of the other dimensions are not important).

            The dimension Filter Example is how I would like to have my filter.

             

            Thanks!

            • 4. Re: Create a filter with 19 boolean dimensions COMBINED
              Chris McClellan

              I don't have an answer yet, but I'm checking to see if I understand the problem properly.

               

              In your example if you choose ACCESSO_DI_DISABILI and AEROPORTO both as true, your current example shows 105,482,993 as the guadagno total, but the proper total that you want to see is 3,112,797 - is that right ?

              • 5. Re: Create a filter with 19 boolean dimensions COMBINED
                Prome nn

                Yes Chris, it's right.

                3,112,797 is the total profit of hotel where ACESSO_AI_DISABILI = TRUE AND AEROPORTO = TRUE (they are services which the hotels provide). I can do this with 19 quick filters but this is not user friendly at all.


                Thanks!

                • 7. Re: Create a filter with 19 boolean dimensions COMBINED
                  Yuriy Fal

                  Hi all,

                   

                  Please find the attached wb.

                  This is a dense variation of "All-Parameters" approach.

                  The mockup is done with only two of them, sorry :-)

                   

                  Hope it could help.

                   

                  Yours,

                  Yuri

                  • 8. Re: Create a filter with 19 boolean dimensions COMBINED
                    Yuriy Fal

                    The subtle but important distinction here

                    is that the state of an unchecked filter

                    should be (All) == NON IMPORTA

                    So it is in essence a 3-state filter (mask).

                     

                    There would be a better implementation in this case,

                    such as a variation of feature vector w/bitmask.

                     

                    Yours,

                    • 9. Re: Create a filter with 19 boolean dimensions COMBINED
                      Yuriy Fal

                      Hi all,

                       

                      I've mocked up an example case

                      using this thread's data as a source.

                       

                      It's a combination of Pivot-LOD-Table Calc.

                      Please find the attached wb and the datasource.

                       

                      Hope to prepare a TabWiki doc soon.

                       

                      Yours,

                      Yuri

                      • 10. Re: Create a filter with 19 boolean dimensions COMBINED
                        Mohamed Yahia

                        Hi Yuriy,

                         

                        Great approach! I've been looking for this for weeks.

                        Can you please provide some instructions on how to implement this? I have a flat sheet with 10 or so dimensional columns. Do I need to restructure the data?

                        • 11. Re: Create a filter with 19 boolean dimensions COMBINED
                          Yuriy Fal

                          Hi Mohamed,

                           

                          Suppose you have a "wide" dataset -- typically a survey --

                          with some columns ("features") could be particular questions.

                          And the answers could be "Yes" / "No" / "Don't know" / "No answer".

                          So you could int-code your answers as 1 / -1 / 0 / NULL.

                          This int-coding could ease data manipulation and compact a dataset.

                           

                          Each row in your "survey" dataset is one survey by a distinct respondent.

                          So you add a "row number" field to the dataset ( [RN] in my example).

                          Having [RN] field is obligatory, 'cause it is used in calculations.

                           

                          Please look at my "example_YF.xlsx" dataset, it has this exact data structure.

                           

                          When you connect to this datasource within Tableau, you'll be using

                          a new v9 Pivot option and apply it to all your "feature" fields.

                          As a result of this transformation you'll have two new fields, namely:

                          "Pivot field names" (String) and "Pivot field values" (Integer).

                           

                          Note that in this "Tableau-Pivoted" dataset each row belongs to

                          a particular "feature" (Question/Answer) from a distinct "survey".

                          Rows with an equal [RN] value are "features" of the same "survey".

                           

                          From that point you could then build a kind of INTERSECTION Filter

                          using [Pivot field names], [Pivot field values] and [RN] fields.

                           

                          The logic behind this filter could be as follows:

                          filter all distinct [RN] for which a COUNTD([Pivot field names])

                          is equal to a MAX of COUNTD([Pivot field names]) selected on a view.

                          This is done using Level-Of-Detail (LOD) calculations (new in Tableau 9).

                           

                          The same approach has been discussed previously (before v9):

                          Brainstorming Multi-Select Filters: INTERSECTION instead of UNION

                          LOD calcs make it rather simple to build then before (with Table Calcs).

                           

                          Note that I've also put an additional filter [Pivot field values] == 1

                          That's because of I'm only interesting in "Yes" answers (which is 1)

                          and ignoring other variants (all others being of no importance).

                           

                          Hope this makes sense.

                           

                           

                          Yours,

                          • 12. Re: Create a filter with 19 boolean dimensions COMBINED
                            Mohamed Yahia

                            Thanks, Yuriy Fal I will implement and ask you if I get stuck.

                             

                            A few questions:

                            1. I would like to stick to 1 spreadsheet as my data source. Will the pivoted data be created from the source or do I have to join it seperately?

                            2. Other than the boolean columns, I have another column, called 'school type' that accepts multiple values: high school, middle school, elementary school, junior high school and some others. I am using a quick filter for that now (all checkboxes appear checked and you have to uncheck each type if you don't want it to appear). That is the reverse of the solution you provided. How can I check this 'school type' column to be like yours?

                            3. Can I have an "Any" or "All" checkbox to go back to viewing everything?

                            • 13. Re: Create a filter with 19 boolean dimensions COMBINED
                              Yuriy Fal

                              1. Pivot is made by Tableau itself, no changes to the datasource whatsoever.

                               

                              2. Are you using this field ('school type') as a "feature" (combining with other "features")

                              or as an attribute (more like a regular dimension)?

                              If former then you could numerate this field's values and include it into Tableau "Pivot".

                              If latter then you'd like to leave it as it is.

                               

                              If you want to apply the "INTERSECTION" logic to the 'school type' field ONLY,

                              you're better to look at the thread mentioned above (here is a link repeated):

                              Brainstorming Multi-Select Filters: INTERSECTION instead of UNION

                               

                              3. To view everything you may want to "Un-check All" in the "Pivot field names" quick filter.

                               

                              Hope this helps.

                               

                              Yours,

                              Yuri

                              • 14. Re: Create a filter with 19 boolean dimensions COMBINED
                                Mohamed Yahia

                                Hi Yuri,

                                 

                                I changed integer coded the boolean columns and pivoted them and put them on a quick filter. I can't get the intersection filter to work. My visualization is different than yours. I have a map. If the feature is checked in the checkbox, I want that dot to appear in the map.

                                 

                                Also, the pivoted columns fall into two categories: applications and hardware. They need to be in separate quick filters. How can I do that now that they are in one pivoted column?

                                 

                                Really appreciate your help here, Yuri. Thanks a million!

                                1 2 Previous Next