3 Replies Latest reply on May 5, 2016 5:03 AM by Brian Wolfe

    Dynamically Filtering by Distance from Target

    Brian Wolfe

      Hi,

       

      I'm struggling with a dynamic filtering issue that I'm hoping someone out there can help with. Below are the details of what I'm trying to accomplish and I've attached a sample Superstore workbook that mimics my original dataset. Any input is appreciated.

       

      Goal: Allow users to select a product purchased by a particular customer from a crosstab dataset and, in a separate worksheet in the same dashboard, show a map of all customers within X miles that have purchased that product where X is a parameter value specified by the user.

       

      Challenge: Thus far I've been able to accomplish everything stated in the goal except for dynamically setting the "central point" around which distance should be calculated. Currently I'm establishing latitude and longitude for the central point based on a ZIP Code parameter. If you select a new value from the "Target ZIP" dropdown, the Map sheet should change accordingly. Ideally I would like users to be able to click a "Product Name" value and have the map visualization update to show customers within the distance filter radius which have purchased the product in question.

       

      Question: Is it possible to change the value of a calculated field based on a selected mark in a dashboard?

       

       

      Thanks,

       

      Brian

        • 1. Re: Dynamically Filtering by Distance from Target
          Rody Zakovich

          Hello Brian,

           

          Want to start off by saying, brilliant work on the Distance Calcs! This looks beautiful!

           

           

          What you are after is made difficult because you are working with Blends, and your secondary datasource doesn't have "Product" in it...

           

          I have an idea how we can get around this, but Blends are not my strongest suite.

           

          I'm going to ping Jonathan Drummey on this.  He is a true Zen, and if anyone can get this to work/give a detail explanation of how....he's the guy!

           

          Regards,

          Rody

          • 2. Re: Dynamically Filtering by Distance from Target
            Jonathan Drummey

            A few notes:

             

            1) The map is plotted out using Tableau's auto-generated lat and longs

            while the calculations are dependent on the lat & long in the data. As much

            as Tableau tries to make their lat & long source accurate they aren't

            necessarily going to be the same as the ones in the data so the distance

            calculations could be different from the plotted locations when you are

            using Tableau's auto-generated lat and longs. My suggestion would be to use

            your lat & long.

             

            2) If you want to use a Filter Action to go from sheet to sheet (where one

            of the target sheets is the map) then the primary source for the map has to

            have Product in it. Rody had already mentioned this.

             

            3) Since the ZipCode Database source has more zip codes than the Superstore

            source, if you want to display all the zip codes and get the Filter Action

            from #2 then you'll need to create a primary source that has all the zip

            codes and all the detail rows. Tableau v9.3 supports a UNION that could be

            used between Excel worksheets, it's also possible to do this with left

            joins in the same source. v10 will have some improvements to this. If you

            don't need to display all the zip codes in the data then you could just

            "flip the blend" for the map and make Superstore the primary and the zip

            code source (for the lats & longs) the secondary.

             

            4) Once you have the sources all set up then you can build out your calcs &

            actions.

             

            5) The view is built using a parameter for picking the center zip code, if

            you want that to be dynamic then you'd need to use something like a

            secondary source for the zip, see

            http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/

            for details. The third example has a use case somewhat similar to yours.

             

            Jonathan

             

             

            On Wed, May 4, 2016 at 12:57 PM, Rody Zakovich <tableaucommunity@tableau.com

            • 3. Re: Dynamically Filtering by Distance from Target
              Brian Wolfe

              Appreciate the replies, gents.

               

              Jonathan Drummey - Your explanation makes sense, but getting the rubber to meet the road is proving to be another story.

               

              Because I only need to reflect the ZIP codes which are present in the Superstore data, I "flipped the blend" as you mentioned in item #3. When I do that, however, I'm unable to get my Central Latitude/Longitude calculations to behave. I'm either getting NULL values in the data set or invalid formula errors due to the need to pull Lat/Long data from the blended data source. I've attached a new version of the workbook showing this and included the short version of my testing and results below.

               

              Any idea what needs to change in order to be able to calculate a central Lat/Long based on the Target ZIP parameter while leveraging the SuperStore data source as primary without adding Lat/Long data to the SS data source itself?

               

              Thanks,

              Brian

               

              Working

                Worksheet: Map CT (Working)

                Field: Central Latitude (ZCD)

                Compute using: Zipcode (Zipcode Database)

               

                Formula:

                WINDOW_AVG(

                ATTR(IF STR([Zipcode]) = [Target ZIP] THEN [Lat] END)

                )

               

               

              Non-working

                Worksheet: Map CT (Non-working)

                Field: Central Latitude (SS)

                Compute using: Advanced (Zipcode, Postal Code)

               

                Formula Attempt #1:

                WINDOW_AVG(

                ATTR(IF STR([ZIPCode Database].[Zipcode]) = [Target ZIP] THEN [ZIPCode Database].[Lat] END)

                )

                Result: Valid formula, NULL values

               

                Formula Attempt #2:

                WINDOW_AVG(

                ATTR(IF STR([Postal Code]) = [Target ZIP] THEN [ZIPCode Database].[Lat] END)

                )

                Result: Invalid formula, "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources

               

                Formula Attempt #3:

                WINDOW_AVG(

                (IF ATTR(STR([Postal Code])) = [Target ZIP] THEN MAX([ZIPCode Database].[Lat]) END)

                )

                Result: Valid formula, NULL values