1 2 Previous Next 23 Replies Latest reply on Jun 16, 2015 6:19 AM by Yuriy Fal

    How to convert multiple boolean columns into two checkbox quick filters

    Mohamed Yahia

      I have locations plotted on a map with several boolean columns to indicate whether types of hardware or applications are available at those locations. Each of these columns has a value of either 1 or -1. Using Yuri Fal's very helpful guidance, I used Tableau 9's pivot feature to pivot those columns and got two dimensions: pivot field names and pivot field values. I have a quick filter that now shows all the column headings as checkbox items.

       

      What I want is this: if the user checks a checkbox, I want the locations with values = 1 for that location to show up. I don't know how to set up the intersection filter.

       

      Also, since these columns logically fall into two separate categories: hardware type and app. I want two quick filters, not one. One for hardware type and the other for app. In each of those filters, I want an 'Any' checkbox that will show all locations.

       

      One more thing: I have counts for hardware type. I created hardware type boolean columns for each type (it's basically a calculated field. If the hardware type count is more than 0, then the value for the corresponding hardware type boolean column is 1. Otherwise, it is -1). I would like to use the hardware type columns in the pivot if I can because I also want to use them to build a pie chart. How can I do that?

       

      Yuri, your help is greatly greatly appreciated!

        • 2. Re: How to convert multiple boolean columns into two checkbox quick filters
          Yuriy Fal

          Hi Mohamed.

           

          Please find the attached wb.

          Here my comments on this:

           

          1) The INTERSECTION logic has been applied

          to [Pivot field names] via a [Filter] LOD calculation.


          2) The selection of Locations with [Pivot field values] == 1

          has been done via [FEATURES] Parameter / [FEATURES Filter] combo.

          These combo allows selecting All OR only Filtered Locations.


          3) [Pivot field names] are grouped into APP and HW groups

          in a distinct Tableau group field. It is used as a Context Filter,

          so when only one group (APP or HW) is selected, another group is ignored,

          which is an equivalent of "un-checked" state of [Pivot field names] filter.

           

          4) Actual Hardware Type counts are included on a Size shelf

          via a bunch of (similar) LOD calcs for each Hardware Type (1-7)

          and a [HW Type] Parameter / [HW Type] Measure combo.


          Note that the SUM of all HW Types is absent, and it is the reason for that.

          Even if one could sum up [HW Type 1] + [HW Type 2] + ... in one calculation,

          the result can not be restricted for only HW Types from the choosen [Pivot field names].


          That's because the INTERSECTION filter is applied to Locations,

          not to the [Pivot filed names] itself. So if a particular Location is on a view,

          all its HW Type (1-7) counts could be summed up regardless of the [Pivot field names] filter.


          Hopes this makes sense.


          Yours,

          Yuri

           

          1 of 1 people found this helpful
          • 3. Re: How to convert multiple boolean columns into two checkbox quick filters
            Yuri Fal

            Just published my reply recently,

            please take a look at the theread.

             

            Yours,

            1 of 1 people found this helpful
            • 4. Re: How to convert multiple boolean columns into two checkbox quick filters
              Mohamed Yahia

              Thanks, Yuri. It looks really nice!! Much better than the series of drop-downs I had before!

               

              Is there a way to make the App and Hardware Type categories separate quick filters with a checkbox option for "Any" to  disable the filtering by that category?

               

              As for Hardware Type counts, I want to use those numbers to create a pie chart on a separate view (hardware type breakdown by location) so my question was: can I include the Hardware Type count columns in the pivot instead of the Hardware Type Boolean columns? I guess the condition for the intersection filter in that case would be include if => 1. Could that work?

              • 5. Re: How to convert multiple boolean columns into two checkbox quick filters
                Yuriy Fal

                Hi Mohamed,

                 

                Please find the attached wb with my best attempt

                of making two distinct Quick Filters (APP & HW).

                 

                You may find an option (NNN Filter is Active) on both QFs,

                which (when selected) turns the other one ON.

                I found this behaviour counter-intuitive at first site,

                but it works as expected when one take a second look.


                As for your second question about HW counts,

                I suppose it should work as expected.

                Please do a pivot and tell me if it works.


                Yours,

                Yuri

                1 of 1 people found this helpful
                • 6. Re: How to convert multiple boolean columns into two checkbox quick filters
                  Mohamed Yahia

                  I was able to pivot the hardware type columns. I changed the filter to a formular: MIN([FEATURES Filter]) >= 1. It seems to be working fine.

                   

                  I need the following modifications, which would make the filters exactly as desired:

                  - Remove (HW Filter is Active) and (APP Filter is Active) from the two quick filters.

                  - Hide the FEATURES parameter control

                  - Make each quick filter inactive with all checkboxes blank by default. Checking a box should filter to only that item. Clearing the filter (by clicking the hour glass with the x next to it) should put the quick filter back to its default state: no checkboxes checked and the filter is not active.

                   

                  Thank you so very much Yuri. You've done a LOT to help me already! Let me know if this is possible.

                  • 7. Re: How to convert multiple boolean columns into two checkbox quick filters
                    Yuriy Fal

                    Hi Mohamed,

                     

                    I don't think it's possible to implement

                    the modifications you've requested (as a whole)

                    without disturbing the view and fooling the user.

                     

                    To be more precise:

                     

                    1) Remove (... Filter is Active) from the QFs

                    mean that either one or the other part of QF -- 

                    either APP or HW, or both --  is filtered out.


                    To "Ignore" a particular QF value is to make it

                    visible on a filter AND in an unselected state.

                    One could rename the Alias for this value (as I did)

                    to make it more or less understandable to the user.

                     

                    2) FEATURES Parameter controls if user wants

                    to show ALL Marks on a view OR only that of QFs.

                    So it is necessary for this Parameter to stay visible on a view.

                     

                    3) One could set up QFs to Include (All) checkbox.

                     

                    But since they are INTERSECTION filters in this case,

                    selecting (All) mean filter only that (rare or non-existent)

                    Marks which has every [Pivot field values] == 1.

                     

                    Un-selecting (All) mean filtering only those Marks

                    for which every [Pivot field values] <> 1 -- none such in this dataset.

                     

                    One could have a blank view either way.

                    If it is a desired view state on opening, that's fine.

                     

                    My point is that one could not mimic a behaviour

                    for which Tableau doesn't have proper interfaces (yet)

                    without (substantial) limitations or (complex) trickery.

                     

                    Yours,

                    Yuri

                    1 of 1 people found this helpful
                    • 8. Re: How to convert multiple boolean columns into two checkbox quick filters
                      Mohamed Yahia

                      Thanks, Yuri. I completely understand what you mean.

                       

                      Is there a way to use one quick filter (as you had originally) but present it as two sections: hardware type and app? Perhaps by using the group feature or by formattting tricks?

                       

                      Also, I may rename the apps and hardware types. It seems the checkboxes are sorted alphabetically. Is there a way to change the sort so that the two sections are separated?

                      • 9. Re: How to convert multiple boolean columns into two checkbox quick filters
                        Yuriy Fal

                        Hi Mohamed.

                         

                        QF is a single object which represents

                        a particular field when on a Filter Shelf.

                        So it can not be divided further.

                         

                        I've used to use a group field in Context

                        just to narrow a scope of [Pivot field names] QF.

                        I could not imagine a more fancy approach, sorry.

                         

                        As for renaming filter values, of course one can do it --

                        via changing Aliases of a respective dimension filed.

                        I've done just that to "rename" a Null value in [APP Filter]

                        to a (HW Filter is Active) value, respectively.

                        So did for [HW Filter], too. Please take a look.

                         

                        And last but not least, you can do change

                        a default Sort Order for a field, please right click on

                        [Pivot field names] --> Default Properties --> Sort ...

                         

                        Hope this helps.

                         

                        Yours,

                        Yuri

                         

                         

                         

                        Удачи!

                        • 10. Re: How to convert multiple boolean columns into two checkbox quick filters
                          Mohamed Yahia

                          Thanks again, Yuri. I understand.

                           

                          In your original workbook, you had pivot field names (group) with HW and App group names. If both of these checkboxes in the quick filter are unchecked and the FEATURES parameter control is active, nothing on the map shows. I thought the checkboxes for the group means we are not filtering by them, not that we are excluding them.

                           

                          Why do you have the pivot field names group anyway? Is it better to remove or hide it?

                           

                          Also, is there a way to keep the map background visible even if there are no matching locations? It goes blank if the intersection filter doesn't have matching results.

                          • 11. Re: How to convert multiple boolean columns into two checkbox quick filters
                            Yuri Fal

                            This is a default behavior of every Tableau Filter.

                            If none is checked -- and hence no Marks to display --

                            then one have a blank view.

                             

                            So if one could see All Marks on a view

                            one should have a special condition (field), 

                            which in this particular case is a combination of

                            FEATURES Filter / FEATURES Parameter.

                            I've described their behavior previously.

                             

                            When FEATURES Parameter is set to 'Filter',

                            then Pivot field names is doing its job.

                            And if nothing is selected in it, then the view is blank.

                             

                            I can not figure any other workaround

                            besides what's been already suggested.

                             

                            Yours,

                            1 of 1 people found this helpful
                            • 12. Re: How to convert multiple boolean columns into two checkbox quick filters
                              Mohamed Yahia

                              Thanks, Yuri. Really appreciate your help!!!!

                              • 13. Re: How to convert multiple boolean columns into two checkbox quick filters
                                Mohamed Yahia

                                Yuri Fal one more thing please!!!!

                                Some of my locations have the same latitude and longitude coordiantes, so I jittered the map using Shawn Wallwork excellent jittering method.

                                 

                                The problem is that the first()==0 you provided removes the jittering. The reason for that is that the detail of the map uses a combined field (lat & long combined), so the first()==0 filter applies to the combined field, not to my location codes. How do I filter the pivot 'duplicate rows' but leave only distinct location codes, not distinct lat&long combinations so that the jittering (the ring of dots in the map) is retained?

                                 

                                PLEASE HELP SOON! THANKS.

                                 

                                Attached is the workbook.

                                • 14. Re: How to convert multiple boolean columns into two checkbox quick filters
                                  Yuriy Fal

                                  Hi Mohamed,

                                   

                                  For the jittering to work as expected,

                                  we'd like to define what a Jitter Cluster is.

                                  In this case the Cluster consists of Marks

                                  with the same Lat/Lon but different Location Codes.

                                   

                                  All jittering calculations are Table Calcs,

                                  so the right addressing / partitioning is the key.

                                  Each Cluster defines a calculation window,

                                  so only the Location Code should be in addressing,

                                  all other dimensions in partitioning -- Lat/Lon included.

                                   

                                  With that in mind, one could add Location Code to the LOD,

                                  and compute all jittering calcs along Location Code.

                                   

                                  All other logic remains untouched.

                                   

                                  Please find the attached wb with modifications.

                                   

                                  Yours,

                                  Yuri

                                  1 2 Previous Next