8 Replies Latest reply on Feb 17, 2016 10:10 AM by Joe Oppelt

    Applying filter on a parameter

    Manjiri Virkar

      Hello,

       

      Currently, I am trying to apply filter on a parameter.In the attached workbook, I am trying to apply “year of expiration” filter on the data which is already filtered based using “select radius” parameter.

      The flow of filters is, first you select a city and then select a radius. For example, you selected “New York – 5” as city and “select radius” as 300 miles, then in the table you get three cities: New York, Washington and Hopkinton. Now for these three regions I want to filter based on “year of expiration”. So when you select 2017, 2019 or Null as “year of expiration”, only Washington City, New York or only Hopkinton (Not Austin)  should appear in the table and when I select 2018 or any other year of expiration, no data should appear in the table.

       

      Problem 2: Can we make “Select City” as a filter? If I have a country filter where I select country, then “Select City” should show me only relevant cities belonging to respective countries. Currently it shows all the cities as it is a parameter. Is there any make to show only relevant cities or “Enter Text to search” functionality with all relevant data displayed in dropdown for a parameter?

       

      Attached Tableau workbook: version 9.0.1

       

      Thank you!

       

      Regards,

      Manjiri

        • 1. Re: Applying filter on a parameter
          Joe Oppelt

          What is happening here is that you are using YEAR as a filter.  So when you grab NULL, for example, you change the underlying table to look at only Hopkinton and Austin.  New York is no longer part of anything being considered in any of the equations, even though your Select City parameter wants to look at New York.

           

          Your [Distance from Origin] calc is a table calc.  (Lookup is a table calc function.)  Table calcs in a filter do not throw out rows in the table.  All selected rows remain, and just the rows that fit the filter get displayed.  But all the underlying rows are still there.

           

          I would change YEAR to a parameter.  (Note:  Params limit you to only one selected value, but that's how you're using it on this dashboard, so it will work here.)  You can have "All" as a value in your param list, and you'll just have to have some IF-logic to decide whether to get all data or a specific value.


          See the attached.  I made YEAR a parameter, and I added logic into the [Filter Distance] calc to incorporate the YEAR param.  This calc is a table calc because it uses [Distance from origin], which is a table calc.

          • 2. Re: Applying filter on a parameter
            Joe Oppelt

            As for your second part, if you make Select City as a filter instead of a parameter, you get the same problem.  It would only grab the records for that city into the table.  You want all cities in the table, but just display those that fit the criteria.

            • 3. Re: Applying filter on a parameter
              Manjiri Virkar

              Thank Joe!

              It works perfect!

              For the second part, since we cannot make the "select city" as filter, is there any other way, where for the "select city" filter I can get only relevant data rather than getting all the cities displayed in the drop down?

               

              I would be having countries all over the globe and it displays all the cities within that drop down, which is not an appropriate user experience. Is there any other work around?

              • 4. Re: Applying filter on a parameter
                Joe Oppelt

                I keep looking at this, and I can't figure out how/where you are using [Select City] on sheet 1.  I'm baffled.

                 

                I might be able to see a different way to pare down a Select City list, but first I need to see how this is getting used.  Can you point this out for me?

                • 5. Re: Applying filter on a parameter
                  Manjiri Virkar

                  Sorry about that. If you see Dashboard 1, I have clubbed the two worksheets together. In future, I would be having many cities in the select filter parameter. The flow would be, I would select a country (for example US), then I select a city from "select City" parameter. In this case, only cities belonging to US should appear and not all cities. What can be done to achieve this?

                   

                  Also, in case I have to add another filter say "Vacancy" filter, how can I add that in the "filter distance"? Once I "Select Radius" and after that I select "Year", I want to select a "Vacancy". How can I add a Vacancy filter/parameter?

                  • 6. Re: Applying filter on a parameter
                    Joe Oppelt

                    That doesn't answer my question.


                    When I'm on Sheet 1, if I select Chicago, somehow it knows on the sheet to focus on Chicago.  I can't see where that impacts the sheet.  What calc or filter tells the sheet to use Chicago?  It has to be getting picked up somewhere.  And the [Distance from Origin] knows that the selected city is the origin.  (Distance for that city is always zero, as we would expect.)


                    That's what I'm asking about.

                    • 7. Re: Applying filter on a parameter
                      Manjiri Virkar

                      When you are on Sheet 1, and if you select Chicago, then value in "Select City" parameter is Chicago.

                      When you pull the radius to say 500 miles, then, the "Distance from origin" formula calculates the distance of 500 miles from the origin city which is Chicago, which is displayed by "Filter Distance".

                       

                      Does that answer your question?

                      • 8. Re: Applying filter on a parameter
                        Joe Oppelt

                        I can see it working that way.  I just can't find what calc or what filter or anything else that actually uses the value of the parameter.  I need to find how it's being used so that I can see if I can find another way to get a value into the machinery.