Home arrow Tutorials arrow Normalize and convert to hall of fame reports

CONTENT WARNING

This site refers to our older, server-based product. If you are interested in our new, Excel-based product, please see our main web site at http://www.nextanalytics.com

Normalize and convert to hall of fame reports
The Hall of Fame report - who was in the top 10 this month and what other months have they achieved this honor.  This is a highly requested report that is difficult to produce with conventional tools, but nextanalytics makes it easy!

Load the sales data

Script
  1. adddata , actuals.csv

We're going to use Actuals data to demonstrate a unique way to look at data.

Name Region Position 01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
Ted Burton Central Account Manager 33,900 62,100 106,200 37,100 115,600 151,700 58,500 102,400 177,700 83,000 133,600 210,400
Nell Gordon Central Sr. Account Manager 87,200 172,700 300,100 85,700 49,400 47,500 68,700 11,400 123,200 109,200 202,100 152,400
Bonnie Mills Central Account Manager 25,800 75,100 77,300 71,500 32,900 14,700 44,000 13,400 227,000 98,400 120,000 173,800
Juan Perez Central Account Manager 25,000 32,900 117,600 67,900 104,600 62,700 55,600 54,800 18,000 18,500 109,600 233,000
Jonathan Ryan Central Account Representative 0 0 0 0 0 0 16,600 32,000 34,800 6,700 4,800 84,800
Marcus Saul Central Account Manager 25,000 46,000 22,500 25,800 76,400 199,200 11,100 67,800 203,300 44,100 65,300 193,500
Jerry Wong Central Sr. Account Manager 99,000 159,700 63,200 7,200 169,700 286,200 83,700 231,600 251,700 155,600 208,000 314,900
Will Hill Federal Account Representative 16,200 15,700 22,300 13,500 43,100 29,900 11,900 46,600 44,200 32,000 6,100 122,600
Gil Jacobs Federal Account Manager 57,600 42,500 88,200 23,600 74,700 186,400 66,400 113,700 214,900 18,300 142,700 198,700
Larry Kelley Federal Account Manager 1,200 22,900 97,400 68,700 99,200 38,100 86,900 107,600 31,700 63,900 109,500 203,800

 

Rank the people based on the last month sales 

Script (continued)
  1. Sort , Last , True
  2. ChangeValuesToRank,False,Column
  3. Select,Column,Last
  4. ChangeAlertTo,relativecell,-1,0,False,-1,False,0,True,reversearrow,-1

This is data ranked down each column, sorted from the best to the worst in the last column. Not only does this show the rankings for the last month, but it also shows the rankings for prior months. The alert coding shows differences from left to right. If they dropped in rankings from the previous month, it is red. If it is green, they went up.

Name Region Position 01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
Stephan Lauzon Northeast Sr. Account Manager 1 36 4 17 8 16 31 5 46 10 28 1
Ricki Murphy Southeast Sr. Account Manager 27 23 2 6 1 24 2 17 6 22 12 2
Jerry Wong Central Sr. Account Manager 2 4 29 41 4 2 6 1 4 1 3 3
Susan Ball West Sr. Account Manager 4 6 34 35 5 10 17 38 3 2 1 4
John Wells International Sr. Account Manager 17 16 31 13 3 4 1 13 29 8 5 5
Juan Perez Central Account Manager 24 33 15 10 17 33 18 31 44 42 13 6
Mike Chang Northeast Account Manager 18 15 41 31 18 39 20 8 13 12 37 7
John Smith Southeast Account Manager 30 11 28 39 31 13 4 38 42 9 39 8
Connie O'Brien Southeast Account Manager 9 28 13 28 12 19 33 16 39 13 30 9
Ted Burton Central Account Manager 22 21 16 21 11 21 16 15 11 14 9 10

 

Show the month's top preformers

Script (continued)
  1. FilterByValue,KeepByVal,LessThan,Row,-1,01/12/2006,False,0,11,True,Actual
  2. ChangeAlertTo,none,,-1
  3. SwapCellValues,Row

After filtering for only the persons who've scored in the top ten in the last month, we can readily see which other months they scored highly.

Rank 01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
1 Stephan Lauzon Ricki Murphy John Wells Jerry Wong Jerry Wong Susan Ball Stephan Lauzon
2 Jerry Wong Ricki Murphy Jerry Wong Ricki Murphy Susan Ball Ricki Murphy
3 John Wells Susan Ball Jerry Wong Jerry Wong
4 Susan Ball Jerry Wong Stephan Lauzon Jerry Wong John Wells John Smith Jerry Wong Susan Ball
5 Susan Ball Stephan Lauzon John Wells John Wells
6 Susan Ball Ricki Murphy Jerry Wong Ricki Murphy Juan Perez
7 Mike Chang
8 Stephan Lauzon Mike Chang John Wells John Smith
9 Connie O'Brien John Smith Ted Burton Connie O'Brien
10 Juan Perez Susan Ball Stephan Lauzon Ted Burton

 

Personal performance perspective

Script (continued)
  1. Select,Page,3
  2. SwapCellValues,Column
  3. SortByLabel , column , true , true , 1~2~3~4~5~6~7~8~9~10
  4. changelabelcaption,False,Column,1,Other Top Scoring Months
  5. changelabelcaption,False,Column,2,Other Runner-up Months
  6. changelabelcaption,False,Column,3,Months in 3rd place
  7. changelabelcaption,False,Column,4,4th place
  8. changelabelcaption,False,Column,5,5th place
  9. ;------------
  10. Select , Page , 1
  11. select,column,Last
  12. MoveColumns,Copy,6,0,First,,Received columns

Here we swap the months into the cells instead of the names so we can see which months each individual has previously placed in the top ten. See how easily nextanalytics lets us go one step further to copy the actual sales figures column from a previous page.

Name 12/06 Other Top Scoring Months Other Runner-up Months Months in 3rd place 4th place 5th place 6 7 8 9 10
Stephan Lauzon 389,400 01/06; 12/06 03/06 08/06 05/06 10/06
Ricki Murphy 327,600 05/06 03/06; 07/06; 12/06 04/06; 09/06
Jerry Wong 314,900 08/06; 10/06 01/06; 06/06 11/06; 12/06 02/06; 05/06; 09/06 07/06
Susan Ball 276,600 11/06 10/06 09/06 01/06; 12/06 05/06 02/06 06/06
John Wells 250,400 07/06 05/06 06/06 11/06; 12/06 10/06
Juan Perez 233,000 12/06 04/06
Mike Chang 228,000 12/06 08/06
John Smith 221,800 07/06 12/06 10/06
Connie O'Brien 211,400 01/06; 12/06
Ted Burton 210,400 11/06 12/06