2 Replies Latest reply on Feb 19, 2016 11:28 AM by Manjiri Virkar

    Applying filters on Parameters

    Manjiri Virkar

      Hi,

       

      In the attached workbook, I am using “Select City”, “Select Radius” and “Year” as parameters. The flow of the filters/parameters is as follows:

       

      First I select a county, then I select a city (for example Hopkinton -1), residing in that country. Then I get a list of records in map and table. To filter further, I give a radius of about 500 miles. After adding this filter, it will show only those records which are 500 miles away from Hopkinton-1. Now I can filter these records based on their year of expiration which is again a parameter.

       

      Now I want to add vacancy and Organization filter for the radius and year filtered records. After applying 500 miles filter I get Hopkinton, New York and Washington records. However, when I apply the org filter and check only test1, I get Austin as output, while it should be only New York and Washington.

       

      I tried to create vacancy and org to be as parameters, but I got stuck with formulas. Can anyone please help me out with this?

      Measures used:

      Distance from Origin: used to calculate distance

      Filter Distance: used to show only those records which are less than the selected miles(select radius) from the origin city(select city)

       

      Workbook: 9.0.1

       

      Thanks & Regards,

      Manjiri

        • 1. Re: Applying filters on Parameters
          Joe Oppelt

          Manjiri -- The issue with using a filter on Org is the same issue you were having with using a filter on Year.

           

          When you use a filter on a data field, it throws out all the records that do not satisfy the filter.  (Including the Selected City, if it does not satisfy the filter!)  That changes all the other calc values on the sheet.

           

          In the attached I did the same thing with Org that we did in the previous thread for Year.  I made a parameter, and then in the [Filter Distance] calc where we added the check for Year, I imbedded an additional check for [Org].  If you're going to add other filters as well, just keep nesting IF logic to that calc so that when all conditions are met, you get a value of 1, otherwise you get a value of 0.  Because it's a table calc and is being used on the filter shelf, it just governs what actually gets displayed, not what rows get included/excluded from the calcs on the sheet.

           

          I also made a change to the [Distance from Origin] calc.  When you select Hopkinton, you probably noticed that it wasn't displaying.  I didn't reverse engineer that calc to figure out what it is doing, but for some reason it was ending up with a null value for Hopkinton's distance when Hopkinton gets selected.  (It was doing New York perfectly well.  If you select New York, you get a value of zero there.)  I wrapped the whole calc in the ZN() function, which says to return zero if the final result is null.  Because the value was null, Tableau wasn't displaying the mark at all for Hopkinton.  Now it does.

           

          Anyway, see the attached for how I handled [Org] in there.

          • 2. Re: Applying filters on Parameters
            Manjiri Virkar

            Thanks Joe! I guess I had messed up with formulas...