Example: Calculate Distances for Territory Optimization

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.

Prerequisites

Security: Model
permission.

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.

Navigation

From the nav menu, select Modeling
.

Steps

Create a dimension for sales account ZIP codes.

Create a dimension for the sales representative ZIP codes.

Add the calculation to the modeled sheet:

Add the dimensions to the modeled sheet.

Create a modeled calculated account.

Add a display column for distance.

View the sheet to see the calculated distances.

Create a Dimension for Sales Account ZIP Code

From the Modeling menus, select Dimensions
.

Create an AccountZipCode dimension and uncheck list dimension
in the dimension details. This dimension must be hierarchical.

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.

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.

Create a Dimension for Sales Representative ZIP Codes

Create a RepZipCode dimension and uncheck list dimension
in the dimension details. This dimension must be hierarchical.

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.

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.

Add the ZIP Code Dimensions to Sheet

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

Select the Edit link next to the modeled sheet you want to include the distance calculation.

Select Columns and Levels
.

Drag the
AccountZipCode
dimension for your sales account ZIP codes into the modeled sheet builder canvas
.

Drag the
RepZipCode
dimension for your sales representative ZIP codes underneath your AccountZipCode in the modeled sheet builder canvas.

Save the sheet.

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.

Use the breadcrumbs, to return to the Summary.

Select Modeled Accounts
.

From the toolbar, click Create new calculated account
and enter the account details:

Code: Distance

Name: Distance (m)

Type: Calculated (periodic)

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

Save the account.

Add a Display Column for Distance

Use the breadcrumbs to return to the sheet Summary.

Select Columns and Levels
.

From the list of Elements, expand Display Columns.

Drag Distance
(m) to the canvas just beneath the RepZipCode dimension.

Save.

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.