Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Calculate Distances for Territory Optimization

Explains how to automatically calculate the distance between two locations and display that distance in a modeled sheet for sales planning within Adaptive Insights for Sales.

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

  • Required permission: Model Management Access > Model
  • 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.

How You Get There

Compass.png From the nav menu, select Modeling.

Basic Steps

  1. Create a dimension for sales account ZIP codes.
  2. Create a dimension for the sales representative ZIP codes.
  3. Add the calculation to the modeled sheet:
    1. Add the dimensions to the modeled sheet.
    2. Create a modeled calculated account. 
    3. Add a display column for distance.
  4. View the sheet to see the calculated distances.

Create a Dimension for Sales Account ZIP Code

Compass.png From the Modeling menus, select Dimensions.

  1. Create an AccountZipCode dimension and uncheck list dimension in the dimension details. This dimension must be hierarchical.
  2. 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.
  3. 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 Dimension for Sales Representative ZIP Codes

  1. Create a RepZipCode dimension and uncheck list dimension in the dimension details. This dimension must be hierarchical.
  2. 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.
  3. 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 Calculation to a Modeled Sheet

Add the ZIP Code Dimensions 

Compass.png From the breadcrumbs, select Modeling. From the Sheets menu, select User Assigned Sheets or Level Assigned Sheets.

  1. Select the Edit link next to the modeled sheet you want to include the distance calculation.
  2. Select Columns and Levels.
  3. Drag the AccountZipCode dimension for your sales account ZIP codes into the modeled sheet builder canvas.
  4. Drag the RepZipCode dimension for your sales representative ZIP codes underneath your AccountZipCode in the modeled sheet builder canvas.
  5. Save the sheet.

Add Zip Code Dimensions

Create a Modeled 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. Use the breadcrumbs, to return to the Summary.
  2. Select Modeled Accounts.
  3. Select Create new calculated account CreateNewCalculatedAccount.png  and give it the account details:
    • Code: Distance
    • Name: Distance (m)
    • Type: Calculated (periodic)
  4. 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 account.

Add a  Display Column for Distance

  1. Use the breadcrumbs to return to the sheet Summary.
  2. Select Columns and Levels.
  3. From the list of Elements, expand Display Columns.
  4. Drag Distance (m) to the canvas just beneath the RepZipCode dimension.
  5. Save.

ZIP Code Dimensions and Distance Display Column

View the Sheet to See the Distances

From the  nav menu, select Sheets and open 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?