CALCULATE

Class

Table Basics

Purpose

Computes certain financial and statistical calculations for a range of records

Syntax

CALCULATE [<scope>] <options list>

[FOR <condition>]

[WHILE <condition>]

[TO <memvar-list> ]

[TO ARRAY <array name>]

See Also

AVERAGE, COUNT, PERCENT(), REPORT, SUM, TOTAL

Description

The CALCULATE command computes certain financial and statistical functions against the records of the current and related tables. If no <scope> is specified, all records will be processed. If a WHILE <condition> is specified, the <scope> will default to REST.

The options list consists of one or more of the following CALCULATE functions:

Function	Description
AVG(<expN>)	Returns the arithmetic mean of <expN>.
CNT()	Returns the number of records selected during a calculate command.
MAX(<expN>)	Returns the maximum number of <expN>.
MIN(<expN>)	Returns the minimum number of <expN>.
NPV(<expN1>,<expN2>,<expN3>)	Calculates net present. The value <expN1> is the interest percentage represented by a decimal number. (For example, 0.15 is 15%). The value <expN2> represents the periodic payment amounts. <expN3> is a numeric value that represents an initial investment. The initial value should be a negative number since it represents cash outflow
STD(<expN>)	Calculates the standard deviation of <expN>. Standard deviation measures the degree to which each individual score in a sample varies from the mean of the scores in the sample
SUM(<expN>)	Returns the sum of <expN>.
VAR(<expN>)	Calculates the population variance of <expN>

FOR <condition>

If a FOR <condition> is specified, only those records matching the <condition> are included in the calculations.

WHILE <condition>

The WHILE <condition> can be used in conjunction with the FIND or SEEK commands, and the REST <scope>, to restrict the number of records which are processed and therefore optimize the performance of the CALCULATE command.

TO <memvar>

The results of the CALCULATE operation will be displayed on the screen if the TO <memvar-list> list clause is not specified and SET TALK is ON. If SET TALK is OFF, the results will not be displayed, so the <memvar list> must be specified to make the results accessible. The memory variables do not need to be predefined. The memory variables are specified in a comma-separated list and their number must match the number of functions used

TO <array>

The TO ARRAY clause is used to store results in a pre-defined one-dimensional array. The result of the first numeric expression is placed in the first array element, the second result is placed in the second element, and so on. If there are fewer elements than expressions, the CALCULATE command will only store results for which there are elements. If there are more elements than expressions, the remaining elements are left empty.

Example

// Calculate the average and variance.
use accounts
seek "a1456"
calculate;
  cnt(),avg(ord_value),sum (ord_value),;
  var(ord_value) to cnt,avg,sum,var;
  while acc_no="A1456"
// Calculate the net present value.
use payments
m_rate = 0.12
m_initial = -40000
calculate npv(m_rate, payment, m_initial) to m_npv

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer