CALCULATE
Performs financial and statistical operations for values of records in the current table.
Syntax
CALCULATE <function list>
[<scope>]
[FOR <condition 1>]
[WHILE <condition 2>]
[TO <memvar list> | TO ARRAY <array>]
<function list>
You can use one or more of the following functions:
|
Function |
Purpose |
|
AVG(<expN>) |
Calculates the average of the specified numeric expression. |
|
CNT( ) |
Counts the number of records in the current table. |
|
MAX(<expC> | <expN> | |
Calculates the maximum value of the specified numeric, character, or date expression. |
|
MIN(<expC> | <expN> | |
Calculates the minimum value of the specified numeric, character, or date expression. |
|
NPV(<expN 1>, <expN 2> |
Calculates the net present value of the numeric values in <expN 2>; <expN 1> is the periodic interest rate, expressed as a decimal; <expN 3> is the initial investment and is generally a negative number. |
|
STD(<expN>) |
Calculates the standard deviation of the specified numeric expression. |
|
SUM(<expN>) |
Calculates the sum of the specified numeric expression. |
|
VAR(<expN>) |
Calculates the variance of the specified numeric expression. |
<scope>
FOR <condition 1>
WHILE <condition 2>
The scope of the command. The default scope is ALL.
TO <memvar list> | TO ARRAY <array>
Initializes and stores the results to the variables (or properties) of <memvar list> or stores results to the existing array <array>. <array> can be a single- or multidimensional array; the array elements are accessed via their element numbers, not their subscripts.
Description
CALCULATE uses one or more of the eight associated functions listed in the previous table to calculate and store sums, maximums, minimums, averages, variances, standard deviations, or net present values of specified expressions. The expressions are usually, but not required to be, based on fields in the current table. You can calculate values in a work area other than the current work area if you set a relation between the work areas.
CALCULATE can also return the count or number of records in the current table. These special functions, with the exception of MAX( ) and MIN( ), can be used only with CALCULATE.
CALCULATE can use the same function on different expressions or different functions on the same expression. For instance, if your table contains a Salary field and a Bonus field, you can issue the command:
calculate sum(SALARY), sum(BONUS), avg(SALARY), avg(12 * (SALARY + BONUS))
CALCULATE stores results to variables or to an existing array in the order of the specified functions. If you store the results to memory variables, specify the same number of variables as the number of functions in the CALCULATE command line. If you store the values in an array, the array must already exist, and the array must contain at least as many elements as the number calculations.
If SET TALK is ON, CALCULATE displays the results in the result pane of the Command window. The SET DECIMALS setting determines the number of decimal places that CALCULATE displays.
CALCULATE treats a blank numeric field as containing 0 and includes the field in its calculations. For example, if you calculate the average of a numeric field in a table containing ten records, five of which are blank, CALCULATE divides the sum by 10 to find the average. Furthermore, if you calculate the minimum of the same table field and five records contain positive non-zero numbers and the five others are blank in the same fields, CALCULATE returns 0 as the minimum. If you want to exclude blank fields when using CALCULATE, be sure to specify a condition such as FOR .NOT. ISBLANK(numfield).
When calculating an empty column, CALCULATE works differently on level 7 tables than it does on table levels less than 7. With level 7 tables, CALCULATE returns "null" on an empty column. For an empty column in tables with a level less than 7, CALCULATE returns 0.
Although you can use the SUM or AVERAGE commands to find sums and averages, if you are mixing sums and averages, CALCULATE is faster because it runs through the table just once while making all specified calculations.
OODML
Loop through the rowset to calculate the values.