Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Calculate Distances for Territory Optimization

Only available with Adaptive Insights for Sales

Knowing the distance between sales representatives and their territories helps during sales territory assignment. By assigning representatives to nearby territories, you can:

  • Reduce costs
  • Reduce travel time
  • Speed up sales cycles

To calculate distances automatically, associate territories and representatives with a ZIP code dimension value. Then, provide geographic coordinates in the ZIP code description fields. Generate the distance in miles between the ZIP codes with a trigonometric formula in a calculated account. Show the result of the distance calculation in a display column in your modeled sheet.

Before You Begin

Obtain the latitude and longitude for the ZIP codes of your sales account territories and your sales representative locations. Multiple on-line sources provide ZIP code coordinates.

Create a Sales Account ZIP Code Dimension

  1. Navigate to Modeling > Model Management > Dimensions.
  2. Create an AccountZipCode dimension and uncheck list dimension in the dimension details. This dimension must be hierarchical.
  3. Create a dimension value named exactly like each sales account ZIP code. For example, if the ZIP code is 11411, the dimension value would be named 11411.
  4. Enter the coordinates for each ZIP code in that dimension value's Description field. For example, the coordinates for ZIP code 11411 are  40.694021,-73.736216. 

ZIP Codes with Coordinates in the Dimension Value Description Field

Create a Sales Representative ZIP Code Dimension

  1. Navigate to Modeling > Model Management > Dimensions.
  2. Create a RepZipCode dimension and uncheck list dimension in the dimension details. This dimension must be hierarchical.
  3. Create a dimension value named exactly like each sales representative ZIP code. For example, if the ZIP code is 11422, the dimension value would be named 11422.
  4. Enter the coordinates for each ZIP code in that dimension value's Description field. For example, the coordinates for ZIP code 11422 are  40.66006,-73.736012.

Sales Representative Zip Code Coordinates

Add the Dimensions to a Modeled Sheet

  1. Navigate to Modeling > Model Management > Level Assigned Sheets.
  2. Select the level assigned sheet you want to include the distance calculation.
  3. Click Columns and Levels.
  4. Drag the AccountZipCode dimension for your sales account ZIP codes into the modeled sheet builder canvas.
  5. Drag the RepZipCode dimension for your sales representative ZIP codes underneath your AccountZipCode in the modeled sheet builder canvas.
  6. Save the sheet.

Add Zip Code Dimensions

Create a Modeled Sheet Calculated Account

This account calculates the distance between the two ZIP codes by evaluating the coordinates you provide in the description fields. As an example, the general formula to evaluate the distance between two sets of coordinates requires trigonometric functions:

ACOS(
    COS(
        RADIANS(90-Lat1))
        *COS(
            RADIANS(90-Lat2))
        +SIN(
            RADIANS(90-Lat1))
        *SIN(RADIANS(90-Lat2))
        *COS(
            RADIANS(Long1-Long2))) 
* 3958.756

Lat1 and Long1 are the latitude and longitude for the first location, and Lat2 and Long2 are for the second location. Your dimension values for ZIP codes contain the latitude and longitude coordinates in the description. The formula you need in the calculated account requires additional text handling to pull the coordinates from the description field.

  1. Navigate to Modeling > Model Management > Level Assigned Sheets
  2. Select the modeled sheet you added dimensions to in the steps above.
  3. Click Modeled Accounts.
  4. Click CreateNewCalculatedAccount.png Create new calculated account  and give it the account details:
    • Code: Distance
    • Name: Distance (m)
    • Type: Calculated (periodic)
  5. Give the account these data type settings:
    • Display As: Number
    • Formula:
      acos(
          cos(
              radians(90-
                  tonumber(substring(this.AccountZipCode.description,0,search(this.AccountZipCode.description,",")))))
                      * cos(radians(90-
                  tonumber(substring(this.RepZipCode.description,0,search(this.RepZipCode.description,",")))))
                      + sin(radians(90-
                  tonumber(substring(this.AccountZipCode.description,0,search(this.AccountZipCode.description,",")))))
                      * sin(radians(90-
                  tonumber(substring(this.RepZipCode.description,0,search(this.RepZipCode.description,",")))))
                      * cos(radians(    
                  tonumber(substring(this.AccountZipCode.description,search(this.AccountZipCode.description,",")+1,99)) -
                      tonumber(substring(this.RepZipCode.description,search(this.RepZipCode.description,",")+1,99))))
          )
      * 3958.756
      
  • Decimal Places: 4
  1. Save the modeled sheet calculated account.

Add Distance As a Display Column

  1. Navigate to Modeling > Model Management > Level Assigned Sheets.
  2. Select the modeled sheet you added dimensions to in the steps above.
  3. Click Columns and Levels.
  4. Drag the Distance (m) display column into the modeled sheet builder canvas just beneath the RepZipCode dimension.
  5. Save the sheet.

ZIP Code Dimensions and Distance Display Column

View the Sheet to See the Distances

Navigate to the sheet containing your ZIP code dimension columns and the display column. The Distance (m) column shows the distance between the sales account and sales representative ZIP codes.

Distance Calculated Between Two ZIP Codes

 

 

 

  • Was this article helpful?