Need an account? Click to Register
Home arrow Learn arrow Tutorials arrow Distribute by combine and compare similar
Distribute by combine and compare similar
Explore each region's average or total sales quota, or how each individual's quota relates to their region's average or total.

Load sales quotas by quarter

Script
  1. adddata , quotas.csv

This is the quotas data from a spreadsheet or database table. It will be used to show how easy it is to prepare useful reports from a simple table of data by comparing and contrasting records with similar characteristics. Technically stated, records that have the same value in a certain column can easily be compared and contrasted. 

Name Region Position Q1-06 Q2-06 Q3-06 Q4-06
Ted Burton Central Account Manager 264 288 312 336
Nell Gordon Central Sr. Account Manager 440 480 520 560
Bonnie Mills Central Account Manager 264 288 312 336
Juan Perez Central Account Manager 264 288 312 336
Jonathan Ryan Central Account Representative 0 0 156 168
Marcus Saul Central Account Manager 264 288 312 336
Jerry Wong Central Sr. Account Manager 440 480 520 560
Will Hill Federal Account Representative 132 144 156 168
Gil Jacobs Federal Account Manager 264 288 312 336
Larry Kelley Federal Account Manager 264 288 312 336

 

Calculate the average quota for each region

Script (continued)
  1. combinesimilar,Region,Average

This calculated the average quota for each region.

Region Count Q1-06 Q2-06 Q3-06 Q4-06
Central 7 277 302 349 376
Federal 5 273 298 322 347
International 3 323 352 381 411
Northeast 9 259 315 341 367
South 7 258 281 305 352
Southeast 7 251 274 297 320
West 9 274 299 341 367

 

Calculate the total of the quotas for each region

Script (continued)
  1. Select,page,first
  2. CombineSimilar,Region,Sum

This calculated the sum of the quotas for each region.

Region Count Q1-06 Q2-06 Q3-06 Q4-06
Central 7 1,936 2,112 2,444 2,632
Federal 5 1,364 1,488 1,612 1,736
International 3 968 1,056 1,144 1,232
Northeast 9 2,332 2,832 3,068 3,304
South 7 1,804 1,968 2,132 2,464
Southeast 7 1,760 1,920 2,080 2,240
West 9 2,464 2,688 3,068 3,304

 

Express each quota as a percent of the region average

Script (continued)
  1. Select,page,first
  2. CompareSimilar,Region,Average

This calculated the average of each region, and then it showed each of the original records as a percent of that average.

Name Region Position Q1-06 Q2-06 Q3-06 Q4-06
Ted Burton Central Account Manager 95 95 89 89
Nell Gordon Central Sr. Account Manager 159 159 149 149
Bonnie Mills Central Account Manager 95 95 89 89
Juan Perez Central Account Manager 95 95 89 89
Jonathan Ryan Central Account Representative 0 0 45 45
Marcus Saul Central Account Manager 95 95 89 89
Jerry Wong Central Sr. Account Manager 159 159 149 149
Will Hill Federal Account Representative 48 48 48 48
Gil Jacobs Federal Account Manager 97 97 97 97
Larry Kelley Federal Account Manager 97 97 97 97

 

Express each quota as a percent of the region total

Script (continued)
  1. Select,page,first
  2. CompareSimilar,Region,Sum

This calculated the average of each region, and then it showed each of the original records as a percent of that sum.

Name Region Position Q1-06 Q2-06 Q3-06 Q4-06
Ted Burton Central Account Manager 14 14 13 13
Nell Gordon Central Sr. Account Manager 23 23 21 21
Bonnie Mills Central Account Manager 14 14 13 13
Juan Perez Central Account Manager 14 14 13 13
Jonathan Ryan Central Account Representative 0 0 6 6
Marcus Saul Central Account Manager 14 14 13 13
Jerry Wong Central Sr. Account Manager 23 23 21 21
Will Hill Federal Account Representative 10 10 10 10
Gil Jacobs Federal Account Manager 19 19 19 19
Larry Kelley Federal Account Manager 19 19 19 19

 

 
< Prev   Next >