5 Replies Latest reply on Dec 1, 2017 9:17 AM by Matthew McDermott

    Closest Store to Customer

    david.lakso

      I'm trying to find the store that is closest to customer.  I have a list of store locations (with lat, long) and customers (with lat, long). Anyone know of a way to run a report that shows the closest store to each customer?

        • 1. Re: Closest Store to Customer
          Manideep Bhattacharyya

          Hello - I think you have to first find the distance between two points. For that use the following KB article

           

           

          Calculating Distance | Tableau Software

          and then  use a min function of distance and place that in the filter. Please help me by providing a sample data set.

           

          Thanks,

          Manideep

          2 of 2 people found this helpful
          • 2. Re: Closest Store to Customer
            david.lakso

            I played with that formula and others but not getting the desired result.

             

            Attached is a sample set of the data I'm using.  It has ten customers and ten stores.  Trying to determine which store is closest to each customer.  In other examples on the net I see that people are setting up a parameter and searching one store at a time.  I want to have Tableau output a whole list rather than going one by one as my actual data set has thousands of locations.

            • 3. Re: Closest Store to Customer
              Manideep Bhattacharyya

              Hello David - If you don't like to show all your Stores and Customers on Map, but only want the Shortest Output then that also can be easily done. Here in the example shows for every Customer the Close 3 nearby store and the distance. Hope this will help. Here I have changed the Source Query to the following:

               

              SELECT [Sheet1$].[Latitude] AS [Latitude],

                [Sheet1$].[Longitude] AS [Longitude],

                [Sheet1$].[Loc Name] AS [Store Name],

                [Sheet1$].[Loc Type] AS [Store],

               

               

              [Sheet1b$].[Latitude] AS [Latitude1],

                [Sheet1b$].[Loc Name] AS [Customer Name],

                [Sheet1b$].[Loc Type] AS [Customer],

                [Sheet1b$].[Longitude] AS [Longitude1]

              FROM [Sheet1$] INNER JOIN [Sheet1$]

              [Sheet1b$] ON ([Sheet1$].[Loc Name] <> [Sheet1b$].[Loc Name])

              where [Sheet1$].[Loc Type] = 'Store'

              AND [Sheet1b$].[Loc Type] = 'Customer'

               

              • 4. Re: Closest Store to Customer
                david.lakso

                That worked.  Thanks for the help.

                • 5. Re: Closest Store to Customer
                  Matthew McDermott

                  This works great for me when i am using a list of 100 stores and 100 customers.  I am getting a Query to large issue when comparing a large amount of data.  Say 2,500 stores to 100,000 or more customers.  Is there any other way to optimize the query?  Thanks for the help!

                   

                  "Database error 0x80004005: The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result."