Share via

CALCULATE Command

  • Article

Performs financial and statistical operations on fields in a table or on expressions involving fields.

CALCULATE eExpressionList [Scope] [FOR lExpression1] [WHILE lExpression2]
   [TO VarList | TO ARRAY ArrayName] [NOOPTIMIZE] 
   [IN nWorkArea | cTableAlias]

Parameters

  • eExpressionList
    Specifies the expressions that can contain any combination of the following functions:

    AVG(nExpression)

    CNT( )

    MAX(eExpression)

    MIN(eExpression)

    NPV(nExpression1, nExpression2 [, nExpression3])

    STD(nExpression)

    SUM(nExpression)

    VAR(nExpression)

    Functions in the expression list eExpressionList are separated by commas. These functions are specific to CALCULATE and are described in detail later in this section. They should not be confused with similarly named independent functions. For example, CALCULATE MIN( ) is not the same as MIN( ).

  • Scope
    Specifies a range of records used in the calculation. Only the records that fall within the range of records are included in the calculation. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST. For more information on scope clauses, see the Scope Clauses topic. Commands that include Scope operate only on the table in the active work area.

    The default scope for CALCULATE is ALL records.

  • FOR lExpression1
    Specifies that only the records that satisfy the logical condition lExpression1 are included in the calculation. Including a FOR clause conditionally includes records in the calculation, filtering out undesired records.

    Rushmore Query Optimization optimizes a CALCULATE ... FOR query if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause. For information on Rushmore optimizable expressions, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

  • WHILE lExpression2
    Specifies a condition whereby records are included in the calculation as long as the logical expression lExpression2 evaluates to true (.T.).

  • TO VarList
    Specifies one or more variables to which the results of the calculation are stored. If a variable you specify does not exist, Visual FoxPro automatically creates the variable with the name you specify.

  • TO ARRAY ArrayName
    Specifies an array name to which the results of the calculation can be stored. If the array name you specify does not exist, Visual FoxPro automatically creates an array with the name you specify. If the array exists and is not large enough to contain all the results of the calculation, Visual FoxPro automatically increases the size of the array to accommodate the information. If an existing array is larger than necessary, additional elements are left unchanged. Results are stored to the array elements in the order they are specified in the CALCULATE command.

  • NOOPTIMIZE
    Disables Rushmore optimization of CALCULATE. For more information, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

  • AVG(nExpression)
    Computes the arithmetic mean of nExpression. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • CNT( )
    Returns the number of records in the table. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • MAX(eExpression)
    Returns the highest or latest value of eExpression. Within the MAX( ) clause, you can specify any character, date, datetime, numeric, float, integer, double, or currency field, or any expression using fields of these types. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • MIN(eExpression)
    Returns the lowest or earliest value of eExpression. Any character, date, datetime, numeric, float, integer, double, or currency field, or any valid expression using fields of these types, can be included in eExpression. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • NPV(nExpression1, nExpression2 [, nExpression3])
    Computes the net present value of a series of future cash flows discounted at a constant periodic interest rate.

    nExpression1 specifies the interest rate expressed as a decimal value.

    nExpression2 specifies a field, field expression, or a numeric expression representing a series of cash flows. Each cash flow can be either positive or negative. In cases where nExpression2 is a field, the value for each record in the field is considered a cash flow.

    nExpression3 specifies an optional initial investment. If the initial investment is not included, then the initial investment is assumed to occur at the end of the first period. This initial investment is the first record in the field and is negative to represent a cash outflow.

    Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • STD(nExpression)
    Computes the standard deviation of nExpression. The standard deviation measures the degree to which the values of fields or expressions involving fields differ from the average of all the values. The smaller the standard deviation, the less the values vary from the average. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • SUM(nExpression)
    Totals the values of nExpression. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • VAR(nExpression)
    Computes the variance from the average of nExpression. The variance is the standard deviation squared. The smaller the variance, the less the values vary from the average. Only records meeting the Scope and/or optional FOR or WHILE conditions are included in the result.

  • IN nWorkArea | cTableAlias
    Specifies the workarea or table alias affected by the CALCULATE command. Use this clause to specify a workarea or a table outside the current work area.

Remarks

Records containing the null value are not included in the operations that CALCULATE performs.

Example

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
USE orders  && Open Orders table

SET TALK ON
CLEAR
CALCULATE AVG(order_amt), MIN(order_amt), MAX(order_amt)
CALCULATE STD(order_amt), VAR(order_amt) TO gnStd, gnVar

See Also

AVERAGE | COUNT | DIMENSION | FV( ) | MAX( ) | MIN( ) | PAYMENT( ) | PV( ) | SUM