7 Replies Latest reply on Jul 27, 2016 6:09 PM by Carlton Lee

    Filtering across and within fields

    Carlton Lee

      I have a (messy) data set with a number of fields (variables) that each constitute a school district (each defined by that district name) within which each observation is a particular school within that district or null if the school is within another district and thus another field. I need to create a nested filter whereby users can first select their school district and then select their school from within that district. Not quite sure how to do this... any help greatly appreciated!

        • 1. Re: Filtering across and within fields
          Ben Neville

          Let's say you have fields A, B, C, and D, each of which hold District information. There are a lot of ways, concatenation, fancy parameters, etc. with which to do this. I think the easiest is:
          IFNULL(IFNULL(IFNULL([A],[B]),[C]),[D])
          etc.

          • 2. Re: Filtering across and within fields
            Carlton Lee

            I'm sorry, I don't follow you... how would a calculation like this allow the user to select district and school?

            • 3. Re: Filtering across and within fields
              Ben Neville

              As you described it (unless I misunderstood), the problem is that the data is messy. As a result, the district is in a number of fields. Going with my earlier example - it could be in field A, B, C, or D. If you give users a filter on field A where they could select their District, this is perfect... for users whose district is in the A field. But if the user's district is in any of the other fields, they would have to select Null. And this Null selection would include multiple districts. So the first step is to combine the fields.

               

              Make a list of all of the fields which hold the district value, then put them in the syntax I described in my original response. Now use that field as a filter. It should now be a "District Master" field. You can do the same thing with each of the fields which hold the school value.

               

              Just use each field as a filter, and they should be able to select the appropriate values. You can also select the dropdown on the School filter and select "Only Relevant Values". This will ensure that once the user has selected a district, only the corresponding school values appear in the School filter.

               

              Did I understand that correctly?

              • 4. Re: Filtering across and within fields
                Carlton Lee

                Thanks Ben, I see what you're getting at... but to clarify: the districts are not in fields, but rather constitute fields, within which each case will either have the name of the school within that district, or a null if the school for that case is in another district, and thus in another field. So I need a way to first filter for the field (district) and then once that is selected, filter for the school within that field (district).

                 

                As it stands, I created a calculated field per your syntax, but Tableau crashes when I try to put it into a filter...

                • 5. Re: Filtering across and within fields
                  Carlton Lee

                  Here's a snap of some of the data, so you can see the structure:

                  Screen Shot 2016-07-27 at 12.26.25 PM.png

                  • 6. Re: Filtering across and within fields
                    Ben Neville

                    Aaaaah... that's clearer. You're honestly going to be best served here by pivoting the data.

                     

                    The only other way I can think of to do this is to create a parameter which has all of the districts as values. Based on which option the user selects, you look up the corresponding column. But you will have to write out a line for every district, which could be quite tedious... i.e.
                    If [Parameter] = "Carlsbad Unified" THEN [Carlsbad Unified]
                    Then you would place this field as a filter. But again... tedious, and won't scale well, or at all to new districts. Pivoting is a much better option if you can.

                    • 7. Re: Filtering across and within fields
                      Carlton Lee

                      Thanks, Ben. Agreed, data needs to pivot, but the parameter within the filter works for now, even if a bit kludgy.