It looks like the Excel list you attached is a list of store locations?
Do you have a list of customers and their lat/long?
I have "Cust" embedded as part of type. Thanks for your interest.
I'm still a little confused on the data structure and goal:
What do the rows in the Excel represent?
What points are you trying to calculate the distance between?
Each row represents a location of a specific type i.e. either Cust, Wholesale, Mom and Pop, Retail, or To Go. I'd like to calculate the distance between Cust and other types.
Was hoping there was a way for Tableau to handle the data structure w/o having to resort to restructuring in the DB.
I looked at this again this evening and I don't see an easy way to do this entirely with table calculations.
Or, perhaps, it's just a lot easier to do this with custom SQL. Custom SQL doesn't require you to modify the data source, but it does require you to enter some basic SQL---in this case a cross join---when connecting to the data source.
A cross join will create a one-to-many mapping for each customer. Take a subset of your data with two customers, and two types of each store:
In order to find the minimum distance between each customer and each type of store with n customers and m stores, you need n * m rows. With the above data, 16 rows that look like this (I've created a customer ID by concatenating the lat/long):
This is relatively easy to do with a SQL query (relative to table calcs, anyway), although if you're using an Excel, Access or CSV file, which use the Microsoft Jet SQL engine, the format is a bit funky.
A cross join combines every row in the first table with every row in the second table. In Microsoft Jet it looks like this:
SELECT TABLE1.[FieldOne], TABLE2.[FieldTwo] FROM TABLE1,TABLE2
In Tableau, add a new connection by selecting from the top menu bar Data > Connect to Data. For your sample data, select the same Excel worksheet, but choose Custom SQL. Click on the "..." and paste the below formula. The Jet connection is difficult to debug (a missing comma results in "file permission denied" error), and when building this from scratch it helps to go incrementally.
Mapping this to your data:
SELECT sub1.[LAT_NB] & "|" & sub1.[LON_NB] AS [Cust ID], sub1.[LAT_NB] AS [Cust_LAT_NB], sub1.[LON_NB] AS [Cust_LON_NB], sub2.[Type] AS [Store], sub2.[LAT_NB] AS [Store_LAT_NB], sub2.[LON_NB] AS [Store_LON_NB], SQR( ((sub2.[LON_NB]*0.01745 - sub1.[LON_NB]*0.01745) * COS((sub2.[LAT_NB]*0.01745 + sub1.[LAT_NB]*0.01745) / 2) * (sub2.[LON_NB]*0.01745 - sub1.[LON_NB]*0.01745) * COS((sub2.[LAT_NB]*0.01745 + sub1.[LAT_NB]*0.01745) / 2)) + ((sub2.[LAT_NB]*0.01745 - sub1.[LAT_NB]*0.01745) * (sub2.[LAT_NB]*0.01745 - sub1.[LAT_NB]*0.01745))) * 6371 AS [Distance (km)] FROM ( SELECT [Sheet1$].[Type] AS [Type], [Sheet1$].[LAT_NB] AS [LAT_NB], [Sheet1$].[LON_NB] AS [LON_NB] FROM [Sheet1$] WHERE [Sheet1$].[Type] = "Cust" ) AS [sub1], ( SELECT [Sheet1$].[Type] AS [Type], [Sheet1$].[LAT_NB] AS [LAT_NB], [Sheet1$].[LON_NB] AS [LON_NB] FROM [Sheet1$] WHERE [Sheet1$].[Type] <> "Cust" ) AS [sub2]
After creating the Custom SQL connection, it's easy to generate a crosstab of the customer and min distance information by dragging Store to the Columns shelf, Cust ID to the Rows shelf, and Distance (MIN) to the Labels shelf. Note the distances are kilometers.
I'm not sure how you want to display these on a map, but I used the original data source to create a basic map with a circle / mark fo reach store and customer. After double-clicking on LAT_NB and LON_NB, you'll just have one circle, since by default Tableau will average these. To display a mark for each row, you can either
- Convert the lat / lon measures to dimensions by right-clicking on the pills on the Row and Column shelves.
- Create a new field Row ID that is unique to the row level and drop this on the Detail shelf.
Row ID =
STR([LAT_NB]) + "|" + STR([LON_NB])
I used the Row ID approach, because I wanted to add the min distance in the tooltip. Since min distance is stored in the Custom SQL table, this requires blending and a linking field. After creating Row ID, Select Data > Edit Relationships and select Custom > Add...
Next, in the Custom SQL data source Data pane, I created fields for the Distance to Wholesale, Distance to Mom-Pop, ... =
IF [Store] == 'Mom_Pop' THEN [Distance] END
And I added these to the tooltip (notice the orange bar and database symbol on the pills indicating these are from the secondary, Custom SQL data source). The result looks like this:
Let me know if some of this isn't clear --- or if I've missed your objective.
StoreLocations.twbx.zip 72.9 KB
You're awesome!!! Thanks for providing such a well written and thorough explanation. I've been looking at this for at least a few weeks and this the most comprehensive set of insights. Albeit I'm new to Tableau and the community, this is much more guidance than I was expecting. I'm grateful.
1 of 1 people found this helpful
You're welcome! The Tableau forums are one of the great things about Tableau.
If you'll be calculating these distances for tens of thousands of customers and hundreds of stores, it may be worth looking into a database with GIS features, such as SQL Server or Postgres with PostGIS. ...
Thanks. I'm in an Oracle and Teradata shop currently.
You've quantified the population accurately. I also think there's an opportunity to further segment Cust for this project since a Cust is 'assigned' to a store typically in the state in which they reside; border cities notwithstanding. For example, I don't need to know the distance between a Cust that's in Evanston, IL to a store in Albany, NY. I'd only need to define the Cust distance to stores in within the state of IL.