1 Reply Latest reply on Jun 18, 2018 8:19 PM by swaroop.gantela

    Calculating Distance from Individual Addresses to Multiple Zip Codes Incorrectly Filtering

    Grant Vandenbussche

      Hello,

      I'm using Desktop v10. I'm trying to calculate the distance for each zip codes from multiple individual address. Specifically, I am trying to answer how many zip codes fall within X miles of a particular store. I am using Lat/Long of the store addresses and the zip codes to calculate the distance between the two points.

       

      The error I am experiencing is that each unique store address is only calculating the distance between it (the store address) and the zip code it lies within. For example, store ABC at 123 Main Street, Chicago, 10001 is only calculating it's distance to zip 10001. I would like it to calculate it's distance relative to all of the other zips within the Chicago metro area. My table (as pictured) should be completely filled with values.

       

      I have attached the workbook. So far my process for calculating distance has been as follows:

       

      LOD LAT STORE == {FIXED [Address]:MIN([Latitude])}

      LOD LONG STORE == {FIXED [Address]:MIN([Longitude])}

      LOD LAT ZIP == {FIXED [Zip]:MIN([LAT])}

      LOD LONG ZIP == {FIXED [Zip]:MIN([Long])}

       

      3959 * ACOS

      (

      SIN(RADIANS([LOD LAT ZIP])) * SIN(RADIANS([LOD LAT STORE])) +

      COS(RADIANS([LOD LAT ZIP])) * COS(RADIANS([LOD LAT STORE])) *

      COS(RADIANS([LOD LONG STORE ]) - RADIANS([LOD LONG ZIP]))

      )

       

      Any help is very appreciated. Thank you!