Home
Distribute the data to get counts of values
When you normalize a group (such as dividing them into quartiles based on sales), see how nextanalytics allows you to continue the analysis into the distribution of the results.

Load the sample data

Script
  1. adddata , actuals.csv

We're going to use the Actuals data to demonstrate how to calculate how many times a certain value occurs.  This works best with normalized data, so the first step in our example is to calculate quartiles down the columns (although any normalization on any axis would just as easily served the purpose). 

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,80

 

Putting people into quartiles 

Script (continued)
  1. ChangeValuesToNtile,True,Column,4
  2. ; the GetCountsOfValues command only works from NOD files, so we need to create a temporary file
  3. SavePagetoNod_totemp,temporary file.nod,False
  4. ; we add this file to make it the current page, but we hide it from the end user because it looks exactly the same as the actuals.csv
  5. addData_fromtemp,temporary file.nod

This page shows the quartile ratings down the columns.  The smaller numbers are placed in quartile 1, and the higher numbers are placed in quartile 4.

 

Get the count of people in each quartile

Script (continued)
  1. ; we run the following command, but it results in the columns not being in an order that we want, so we need to sort it
  2. Getcountsofvalues
  3. ; this command sorts the columns in the 1 2 3 4 order.  If there were any other columns, they are dropped.
  4. SortByLabel , column , true , true , 1~2~3~4
  5. ; change the column labels to make the report more readable
  6. newrowlabel,Dates
  7. changelabelcaption,False,Column,1,Count of People in Quartile 1
  8. changelabelcaption,False,Column,2,Count of People in Quartile 2
  9. changelabelcaption,False,Column,3,Count of People in Quartile 3
  10. changelabelcaption,False,Column,4,Count of People in Quartile 4
  11. ; quickly show which cells are above or below the row average
  12. ChangeAlertTo,axis,Current,Row,Average,0,True,arrow,-1

This tells how many people were in each quartile for each data column.  When it is green, it means the cell is above the row average. Red means below average. 

Dates Count of People in Quartile 1 Count of People in Quartile 2 Count of People in Quartile 3 Count of People in Quartile 4
01/06 25 11 7 4
02/06 23 13 6 5
03/06 23 16 4 4
04/06 26 12 7 2
05/06 20 9 14 4
06/06 20 8 17 2
07/06 20 12 12 3
08/06 18 18 6 5
09/06 23 14 8 2
10/06 20 13 11 3

 

Swap rows and columns and repeat 

Script (continued)
  1. Select , page , Actuals
  2. ; exchanges the row and column axis
  3. Swap
  4. newrowlabel,Dates
  5. PageCaption , "Actual Sales, Time by People"
  6. PageDescription , "These is the same data as the Actual page, but the rows and columns have been reversed."

  8. ChangeValuesToNtile,True,Column,4
  9. PageCaption , Time Quartiles
  10. PageDescription , "This page shows the quartile ratings down the columns.  The smaller numbers are placed in quartile 1, and the higher numbers are placed in quartile 4."

  12. ; save the page to a NOD format so that the GetCountsOfValues can be used.
  13. SavePagetoNod_totemp,temporary file.nod,False
  14. addData_fromtemp , temporary file.nod
  15. ; we perform the command, but it results in columns not being in an order that we want.
  16. Getcountsofvalues

  18. newrowlabel,People
  19. SortByLabel , column , true , true , 1~2~3~4
  20. changelabelcaption,False,Column,1,Count of Times in Quartile 1
  21. changelabelcaption,False,Column,2,Count of Times in Quartile 2
  22. changelabelcaption,False,Column,3,Count of Times in Quartile 3
  23. changelabelcaption,False,Column,4,Count of Times in Quartile 4
  24. PageCaption , Count of Time Periods in each Quartile
  25. PageDescription , "This command shows how many times each person had each rating.  When it is green, it means the cell is above the row average. Red means below average."
  26. ChangeAlertTo,axis,Current,Row,Average,0,True,arrow,-1
  27. ;--------------
  28. removenodfile_fromtemp , temporary file.nod

This command shows how many times each person had each rating. When it is green, it means the cell is above the row average. Red means below average.

People Count of Times in Quartile 1 Count of Times in Quartile 2 Count of Times in Quartile 3 Count of Times in Quartile 4
Ted Burton 4 4 2 2
Nell Gordon 4 5 2 1
Bonnie Mills 5 5   2
Juan Perez 8 3   1
Jonathan Ryan 9 2   1
Marcus Saul 6 3   3
Jerry Wong 3 3 3 3
Will Hill 8 3   1
Gil Jacobs 5 3 1 3
Larry Kelley 4 5 2 1

 

 
< Prev   Next >