14 Replies Latest reply on May 20, 2013 6:36 PM by Joshua Milligan

# Distance Formula Calculation relative to each record!

I have a custom object in Salesforce which contains member data , I have geocoded the address to Latitude and Longitude. I am trying to find the distance between each member. I am unable to write a custom query to replicate the Lat and Long fields , as the salesforce connection does not let me do it. Can someone help me with it?

• ###### 1. Re: Distance Formula Calculation relative to each record!

Priya,

I can think of three cases that each have different answers, so it depends on exactly what you want:

1. Are you looking to calculate the distance between each member and a single geographic point?

2. Or you truly do want the distance from each member to every other member?

3. Or do you want to find the distance from member 1 to member 2 then from member 2 to member 3, etc...

1. It would be fairly easy to write a calculation.

2. It would be quite difficult, if not impossible*, without being able to do some kind of join in the source

3. It would be possible to write this using the LOOKUP function.

Or are you trying to do something else?

* But I won't ever say it IS impossible, because there are brilliant people who do the impossible every day on these forums.

Regards,

Joshua

• ###### 2. Re: Distance Formula Calculation relative to each record!

Joshua I think he's trying to get Salesforce to let him do something like this:

http://kb.tableausoftware.com/articles/knowledgebase/calculating-distance

But I could be completely wrong.

--Shawn

• ###### 3. Re: Distance Formula Calculation relative to each record!

Shawn,

I think you are right.  That is case 2, which is going to be difficult (or not possible) using data connections that don't support joining via custom SQL.

I took the example and modified it to show how it might work using LOOKUP instead of joining.  Note that it is now a straight connection to the Excel data and not using custom SQL.  This is case #3.  The difference is that it isn't every location to every other location, but rather one location to the next to the next.  As there are only 3 locations in the example, the solution looks almost the same.  (Except that there is a line missing in the LOOKUP as it goes point 1 to 2 to 3 and doesn't complete the loop back from 3 to 1.

Regards,

Joshua

• ###### 4. Re: Distance Formula Calculation relative to each record!

Hi Joshua,

Thanks a lot for the reply. I am not able to open the zip file you sent over. Can you post the code/formula you used?

Also If in the event I export all the data into excel, how do I create a self join with custom sql, I tried to do it, but it throws an exception. Can you help me with the custom sql for a self join as well?

• ###### 5. Re: Distance Formula Calculation relative to each record!

Here is what I am trying to do:

I have a couple of member records in salesforce with address information with Lat and Long Info as well. I want to connect that to Tableau, and when I type in a member name in the search filter, the distance between with each member and this particular member should show up / be displayed in the map. Is it possible?

Is there a solution to this?

• ###### 7. Re: Distance Formula Calculation relative to each record!

Priya,

What is the error message?  I'm about ready to post another example based on your explanation of the issue.

Regards,

Joshua

• ###### 8. Re: Distance Formula Calculation relative to each record!

Don't know. I just opened it in V7 and V8. You realize you need to extract it using a ZIP utility first, right?

--Shawn

• ###### 9. Re: Distance Formula Calculation relative to each record!

Priya,

Here is a workbook that demonstrates how you can find the distance from a selected customer to all others.  It has some explanation, but if you have any questions I'd be happy to answer!

If you have any trouble with this workbook, please let me know!

Regards,

Joshua

• ###### 10. Re: Distance Formula Calculation relative to each record!

An Error occurred while communicating with the Microsoft Excel file. I am getting this error, whenever I try to download a file from the forum.

• ###### 11. Re: Distance Formula Calculation relative to each record!

Is it possible to email the file to me?

• ###### 12. Re: Distance Formula Calculation relative to each record!

Priya, a week ago or so the download process changed (at least it did for me). Now instead of the downloaded file showing up in the bottom of the Chrome browser, it automatically opens the file in my ZIP utility so it looks like this:

The first time this happened I didn't notice that I was actually inside the ZIP file and I double-clicked the twbx file and got this:

This is because the twbx file is also a ZIP file. It sound like you kept drilling in until you got to the Excel file in the Data folder. Instead of drilling in, you need to hit the 'Extract' button first and then go to the folder you extract it to. Then double-clicking the twbx file should open it.

As I've mentioned before I'm not sure why when the new forums came into being they started zipping zip files and making us go through these extra (and obviously confusing) steps. Something technical with Jive I'm sure.

--Shawn

1 of 1 people found this helpful
• ###### 13. Re: Distance Formula Calculation relative to each record!

Hi Joshua,

The solution was perfect. Just one issue. I tried to replicate your solution to my case, everything seem to work but when the distance is calculated , it is getting calculated with respect to the first record, instead of it changing everytime the member parameter changes, how should I set this straight?

• ###### 14. Re: Distance Formula Calculation relative to each record!

Hi Priya,

With the table calculation, the important thing is to set the Partitioning and Addressing correctly.  If you right click the Distance field on the Marks card, you can select "Edit Table Calculation" to get an options dialog.  Select "Advanced..." for the Compute Using option.

In this case, I don't want to partition the data only address it by Customer.  But the important thing is to sort the data by the [Customer Order] calculated field.  That's what allows the calculation's reference to the First()  function to refer to the selected customer.

Regards,

Joshua

1 of 1 people found this helpful