1 of 1 people found this helpful
I'll take a look at your example and see if I can spot what's happening, but I'll also point you at a few other resources that might be helpful, in case you haven't seen these.
Robert Mundigl has written several blog posts discussing how to do this sort of analysis on his Clearly and Simply site. I wrote some follow-up articles (here's the first of the series - there are links to Robert's original and the rest of mine) discussing ways to get around some issues Robert was having back when Tableau 6 came out. Those articles discuss various approaches to doing the lookup of lat/lon and calculating the distance.
I did a viz of New Zealand earthquakes which includes another slightly different variant of the calculation (in this case the reference points were from another table, rather than being references back to other rows of the original table as in Robert's example - your example sounds more like Robert's).
You can download sample workbooks from Tableau Public from those links.
This thread, which started out discussing something quite unrelated evolved into a long discussion of more efficient ways to do this type of distance calculation if you have access to a database with spatial functionality (SQL Server, Postgres, Oracle, etc). I only mention this because depending on your data volumes, the lookup approach discussed in the first few threads just may not be viable. The lookup approach is fine with thousands or probably even tens of thousands of rows whereas the spatial database approach scales to millions.
I've also just noticed that there are now a couple of Tableau Knowledgebase articles on Calculating Distance and Radius Filters. From a quick skim it sounds as if the second of those might be quite close to what you want.
I'll take a look at the example you posted too.
You were almost there.
The error about mixing aggregate and non-aggregate is a very common one when you are using data blending and it can be a bit confusing until you get your head around what is happening.
Any references to fields from the secondary datasource have to be at the aggregate level. So even though in this case you have used blending to lookup the value of a specific reference customer, you still had to reference the average latitude and longitude. You could just as well reference the MAX() or MIN() or even better the ATTR() - they will all give the same value.
The reason I said ATTR() is even better is that this is a special aggregate that will return the value if all rows have the same value, otherwise it will highlight to you that there are multiple values by displaying a '*' - which can be a useful way of spotting that you have something wrong (if your lookup had returned more than one value in this case).
So as your reference lat and lon are aggregates, you need to make the lat and lon references to your primary data source aggregates, too. Just wrap them in the ATTR() function as I have in the attached.
I also had to put your reference customer ID on level of detail to allow the blend to work.
Tableau Help 12 28_RL.twbx.zip 46.0 KB
Thinking about it, the approach discussed in that knowledgebase article is probably the best way of doing this.
I've added another sheet which shows how to do that, using a new datasource which just joins your original table to itself and has a single-select filter to choose the reference customer. Notice that as this no longer uses data blending you don't have to worry about the aggregate functions in the calculation.
Tableau Help 12 28_RL2.twbx.zip 74.5 KB
I am trying to do exactly the same thing, but the problem is I am trying to connect to Salesforce instead of Excel.
How did you write the distance formula? I am new to Tableau as well. Can you pls help?
If you open the workbook on my original post, the "Distance from Customer" measure contains the distance formula.