Home arrow Tutorials arrow Normalize the cells into their rank values
Normalize the cells into their rank values
See how ranking of data can be used to easily expose the top performers.

Load the sample data 

Script
  1. adddata , actuals.csv

This sample data will be used to show how easy it is to convert a crosstab to a page of rank values. The topmost values are converted to a value of 1 and each subsequent value is given an ascending number. This can be performed down the column, across the row, or over the whole page.

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 each column 

Script (continued)
  1. ChangeValuesToRank,False,Column
  2. assignwordstovalues,false,~*****~*****~****~****~***~***~**~**~*~*~, 12,01/06,02/06,03/06,04/06,05/06,06/06,07/06,08/06,09/06,10/06,11/06,12/06,
  3. ChangeAlertTo,cellvalue,-1,reverse,-1

The numbers are the column ranking. The color is applied using the ability of the Alert engine to look at the cell contents of the current (or another) page. There's a limit of ten on the number of colors assigned, so attention is only given to numbers between 1 thru 10. Higher ranks (lower values) are not colored. In this example, the more green, the better. If a cell is red, it's better than a cell which has no color at all. This would be an excellent place to display gauges.

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                     * *
Nell Gordon Central Sr. Account Manager *** **** **** ****           **** ****  
Bonnie Mills Central Account Manager       **         ** **    
Juan Perez Central Account Manager       *               ***
Jonathan Ryan Central Account Representative                        
Marcus Saul Central Account Manager           **     *      
Jerry Wong Central Sr. Account Manager ***** ****     **** ***** *** ***** **** ***** **** ****
Will Hill Federal Account Representative                        
Gil Jacobs Federal Account Manager                 *   **  
Larry Kelley Federal Account Manager       *     ***          

 

Show the top-ranked in each period

Script (continued)
  1. SwapCellValues,Row
  2. newrowlabel,Ranking

This was accomplished by the SwapCellValues with Row function. It provides a very nice life to date view of the data. Notice that the tie values are handled correctly so that the rank values are assigned correctly.

Ranking 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 Serge Roy Mike Li Anh Nguyen Ricki Murphy Mike Li John Wells Jerry Wong Larry Jones Jerry Wong Susan Ball Stephan Lauzon
***** Jerry Wong Mike Li Ricki Murphy Priscilla Morris Mike Li Jerry Wong Ricki Murphy Larry Jones Priscilla Morris Anh Nguyen; Susan Ball Larry Jones Ricki Murphy
**** Serge Roy Nell Gordon Nell Gordon Nell Gordon John Wells Anh Nguyen Larry Jones Serge Roy Susan Ball   Jerry Wong Jerry Wong
**** Susan Ball Jerry Wong Stephan Lauzon Bill Wright Jerry Wong John Wells John Smith Mike Li Jerry Wong Nell Gordon Nell Gordon Susan Ball
*** Nell Gordon Anh Nguyen Anh Nguyen Serge Roy Susan Ball Larry Jones Larry Kelley Stephan Lauzon Mike Li Mike Li John Wells John Wells
*** Larry Jones Susan Ball Emma Thatcher Ricki Murphy Anh Nguyen Emma Thatcher Jerry Wong Anh Nguyen Ricki Murphy Anne Grenier Mike Li Juan Perez
** Priscilla Morris Anne Grenier Patrick Roberts Bonnie Mills Priscilla Morris Anne Grenier Emma Thatcher Kerry Turner Bonnie Mills Bonnie Mills Gil Jacobs Mike Chang
** Ashok Chopra Mike Anderson Scott Williams Sally Levitt Stephan Lauzon Marcus Saul Serge Roy Mike Chang Sally Levitt John Wells Richard Schwartz John Smith
* Connie O'Brien Phillip White Sally Levitt Larry Kelley Kathy Holmes Kathy Holmes Anne Grenier Marcia Brady Gil Jacobs John Smith Ted Burton Connie O'Brien
* Anne Grenier Ashok Chopra Ashok Chopra Juan Perez Kerry Turner Susan Ball Andy Patterson Scott Williams Marcus Saul Stephan Lauzon Bill Wright Ted Burton