Let's go back to the beginning and do a completely new report.

This time we're going to show you innovative ways to examine sales against quota.

Let's start by opening the Quotas.txt Project from the OpenChooseProject.

To begin, this project uses the quotas.csv file. As a manager, I might keep this file on my own computer so I can edit it with a spreadsheet program.

Because I know the actuals data will be arriving in units of a thousand, I use nextanalytics to multiply the quota data by 1000.

1. Click the Calculate Menu
2. Choose General Math
3. Choose Multiply Selected Cells With Value
4. Choose the number from 100 to 1000
5. Press OK

Now I'll add the Actuals data. In most situations, this would have been obtained from a query to a database, but I'm just going to add a previously saved CSV file.

1. Click the Project Menu
2. Click the Show/Hide Script
3. In the Script Text Box, Append the line AddData , Actuals.csv
4. Press Save/Run

n.b. The AddData command and its parameters are not case-sensitive.

Notice how easily it was for me to intersperse my working with the dialog boxes with script that I can enter by hand or paste from a text file.  Of course, this is optional, but allows me to invoke saved scripts, thus allowing code reusability and avoiding the cloning of repetitive scripts.

You'll notice one thing about the Actuals data, it's monthly. But the Quota data is quarterly. So let's convert the columns in one set of data to match the other.

1. Choose Distribution Menu
2. Select Distribute Column Values...
3. Ensure that Ratio is selected
4. Select Page 1 
5. Press the OK button

Now the number of columns match. The amounts in the Actuals version of the quarters have been pro-rated to the size of the Quota amounts. Take note that for byRatio to work, the number of columns must be divisible by the number of columns in the other page. If this isn't the case, then use the by Average option which spreads the values evenly over the target number of columns.

1. Choose Compare Menu
2. Select To Relative Cell...
3. Change the "left or right" value to 0.
4. Select Page 1
5. Ensure the style of comparison is "Percent Of".
6. Click the OK button

The numbers being shown are percents.

They are the Actuals (from a data base query) being compared to Quotas (from a spreadsheet file).

Now, we'd like to add a visual alert. We want to show who was above or below the column average, in other words, who was above or below the quota achievement for each time period.

1. Choose Alert Menu
2. Select Alert By Comparing To Axis...
3. Ensure Column and Average are selected
4. Press OK

This alert is shows cells that are more than 15% below average as red.  It shows cells more than 15% above average as green.  In between, they are yellow for below, and blue for above.