Home arrow Tutorials arrow Compare as percent of another page

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

Compare as percent of another page
Comparisons to other sets of data are very common - percent of budget, quota achievement, year-over-year performance, and so on. With nextanalytics, these comparisons are a breeze!

Load the sales data

Script
  1. adddata , actuals.csv

This is sample sales 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

 

Load the expenses data

Script (continued)
  1. adddata , expenses.csv

This is sample expenses data.

Product_Name Category Region 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 20 4,279 7,298 5,122 1,497 4,532 2,241 1,099 2,533 7,425 5,918 5,293
Nell Gordon Central Sr. Account Manager 4,443 883 7,077 7,565 4,212 3,865 1,804 3,570 5,122 2,437 6,678 3,532
Bonnie Mills Central Account Manager 795 3,769 7,235 1,664 7,015 3,379 1,507 7,847 4,173 6,473 5,262 2,395
Juan Perez Central Account Manager 6,704 3,700 3,187 6,727 4,260 700 7,563 641 3,060 7,439 6,385 4,042
Jonathan Ryan Central Account Representative 0 0 0 0 0 0 0 725 6,823 5,619 3,636 1,869
Marcus Saul Central Account Manager 901 6,809 1,594 5,144 1,327 6,860 3,990 6,454 1,165 4,494 5,674 7,890
Jerry Wong Central Sr. Account Manager 7,251 2,673 5,598 3,730 2,378 1,718 7,888 4,584 7,124 6,686 3,144 87
Will Hill Federal Account Representative 7,822 5,519 2,394 59 5,790 3,674 5,136 3,031 4,231 4,879 4,453 2,728
Gil Jacobs Federal Account Manager 5,165 7,412 240 3,465 885 3,017 6,327 7,232 2,044 3,379 2,294 335
Larry Kelley Federal Account Manager 1,792 6,287 3,568 4,858 7,256 4,529 1,002 2,139 811 2,816 810 868

 

Compare the expenses to the sales

Script (continued)
  1. Compare,ToRelativeCell,PercentOf,Actuals,Row,,,-1,-1,0,0,False,False,Actuals,

This is a comparison of expenses to sales expressed in percentages.

Product_Name Category Region 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 0 7 7 14 1 3 4 1 1 9 4 3
Nell Gordon Central Sr. Account Manager 5 1 2 9 9 8 3 31 4 2 3 2
Bonnie Mills Central Account Manager 3 5 9 2 21 23 3 59 2 7 4 1
Juan Perez Central Account Manager 27 11 3 10 4 1 14 1 17 40 6 2
Jonathan Ryan Central Account Representative 0 2 20 84 76 2
Marcus Saul Central Account Manager 4 15 7 20 2 3 36 10 1 10 9 4
Jerry Wong Central Sr. Account Manager 7 2 9 52 1 1 9 2 3 4 2 0
Will Hill Federal Account Representative 48 35 11 0 13 12 43 7 10 15 73 2
Gil Jacobs Federal Account Manager 9 17 0 15 1 2 10 6 1 18 2 0
Larry Kelley Federal Account Manager 149 27 4 7 7 12 1 2 3 4 1 0

 

Look for the outliers in the results

Script (continued)
  1. ChangeValuesPercents,True,Column,2,0.2,0.8
  2. assignwordstovalues,false,~~~High~~~~~~~~,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,

This is a comparison of expenses to sales. We normalized it into three buckets of bottom 20%, middle, and above 80%. Then we assigned a word to show instead of the value so that your eye is drawn to only those with the highest expense ratio, i.e. above 80% for that given time period. Since it is a crosstab, it's difficult to see all the people with high ratios, so we are going to take a step further in the next page.

Product_Name Category Region 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 High
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

 

Summarize the outliers

Script (continued)
  1. SwapCellValues,Row
  2. SelectRange,row,0,2
  3. RemoveSelected,true,false
  4. changelabelcaption,False,Row,3,Those With High Expenses

After normalizing the data, we swapped the cell values to the row. Then we removed the first two rows because they contained those with low and middle expense ratios which isn't what we were after, we wanted to know who had HIGH expenses. As a result, this final page has a list of people who were in the top 20% of expense costs in each respective time period.

01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
High Scott Williams Emma Thatcher Jean Rochon Scott Williams Jean Rochon Ralph MacDonald Jake Grey Bill Wright; Jake Grey Justine Pollock Jonathan Ryan; Kathy Holmes Emma Thatcher Larry Jones