3 Replies Latest reply on Jul 16, 2013 1:56 PM by Allan Walker

    Use Parameter to select values for multiple columns?

    David Edwards

      All,

       

           I am having a little trouble figuring out what might be the best / easiest way to proceed to solve the following problem.

       

        We operate a number of locations where customers will travel to receive a service.

        I want to create a 'within 50 miles' type of visualization so that we can easily figure out where and how to divert people in the case of an outage.

       

        What I want to know, is how can I allow the user to select a single location, and show only locations within N miles (parameter) of the selected location?

       

         We have about 8500 locations, and I have so far tried to just join the list to itself, trim it down to only pairs within 100 miles of each other (1.3M pairs), then letting the user filter down from there using a drop down for a 'central' location, then a distance slider to bring it under 100 miles as needed.

       

           This works fine, but there is another dimension that I need to take into account:  What dates the different locations are open.

       

      Because of this last constraint, I would rather not pre-join all the locations to each other (they aren't all open all the time), but rather let the user select one using a parameter, and calculate all of the distances on the fly, filtering based on what dates the user has picked.

       

      What I am wondering, is how can I lookup the LATITUDE and LONGITUDE (or other variables for that matter) for the location that has been selected using the parameter so that I can use these values in the calculations for all the other rows?

       

      Any other advice about trying this would be welcome.

       

      Thanks!

      ~Dave

        • 1. Re: Use Parameter to select values for multiple columns?
          Allan Walker

          Hi Dave,

           

          Calculating distances on the fly between x,y 1 and x,y 2

           

          =ACOS(COS(RADIANS(90-[Lat1])) *COS(RADIANS(90-[Lat2])) +SIN(RADIANS(90-[Lat1])) *SIN(RADIANS(90-[Lat2])) *COS(RADIANS([Long1]-[Long2]))) *6371

          • 2. Re: Use Parameter to select values for multiple columns?
            David Edwards

            Thanks guys for the Geometry lesson.

             

            I'm not actually looking for equations to calculate this distance that part is easy; plus there will need to be a URL action to submit the two addresses to Google Maps anyway. I need the distance between the points mostly as a filter mechanism.

             

            I am looking for tableau calculation techniques and data shaping advice.

             

            For example, if I have a parameter that lets a user select a given site, how do I use values from that site's record in the calculations for all the other sites?  That is to say, can I make a parameter (Select an Institution) also cause a number of values from that row to be calculated fields on all other rows?

             

            For Example:

             

              p_Selected Latitude:

                 IF [INSTITUTION] = [Selected Institution] THEN [LATITUDE] end

            however this only gives the value for the row that is selected.

             

            I have tried a few different Table Calcs (which I'm admittedly awful at); however, those that I have attempted do not seem to give me consistent results.

            • 3. Re: Use Parameter to select values for multiple columns?
              Allan Walker

              Would something like this help conditionally filter the parameter*?

               

              IFNULL([Selcted institution],'Null') = IF [locationparameter] != 'All' THEN [locationparameter] ELSE IFNULL([Selected institution], 'Null') END

               

              *Adding "All" to the parameter?  You might need to go STR([Selected instituion])