Need an account? Click to Register
Home arrow Learn arrow Tutorials arrow Normalize the cells into 20 and 80 percent buckets
Normalize the cells into 20 and 80 percent buckets
A frequent request is to see the top or bottom 20% of a report. nextanalytics lets you go beyond this simple task and actually assign text to the cells to make it easier to interpret the report.

Load the sales data

Script
  1. adddata , actuals.csv

We're going to show the sales data according to three ratings. Below 20%, below 80%, and above. We've been doing mostly examples going down the columns, so for a change, we're going to do this calculation across the row. This will show which months were the best or the worst for each person.

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

 

Split to below 20%, or 80% and above 

Script (continued)
  1. ChangeValuesPercents,True,Row,2,0.2,0.8
  2. assignwordstovalues,false,~Bottom~~Top~~~~~~~~, 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,three,-1 elow 20%, Below 80%, and the Rest

The results of each row were divided into three buckets. A value of 1 means that the value was in the bottom 20%. A value of 2 means the value was below 80%. And a value of 3 means that the cell was above 80%. These become the new numeric values of these cells. But, to show an interesting feature of the product, we're going convert the numeric values to words. We're not going to put text into all cells, only the bottom and top so they stand out. Since the cells actually do contain numeric values of 1, 2 or 3 behind the scenes, we can also use Alert coloring to color them as well.

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

 

 
< Prev   Next >