The calculate statement is used to perform arithmetic operations using literal and column values.
calculate columnResultColas source operation source
where source is either of columncolName or valueliteral_value
and operation is one of the characters + - * / % for addition, subtraction, multiplication, division and modulo respectively.
There must be whitespace on each side of the operation`character
Examples:
calculate column ResultCol as column Amount * value 1.2
calculate column Net as column total - column cogs
calculate column constant_7 as value 3.5 + value 3.5
The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).
Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.
A literal value is specified using valueN where N is the literal number required
A column name is specified using columncolName where ColName is the name of the column containing the values required
The ResultCol may be the same as a column specified by one of the source parameters in which case any existing values in it will be updated with the result of the calculation.
Any blank or non-numeric values in a source column will be treated as 0
An attempt to divide by zero will result in 0
When performing a modulo operation, the two source values are rounded to the nearest integer first
If the result column already exists then if option overwrite is set to no, only blank cells in the result column will be updated.
Add 1.5 to the values in the Rate column:
calculate column Rate as column Rate + value 1.5
Multiply the values in the Rate column by those in the Quantity column
Store the result in a new column called Charge
calculate column Charge as column Rate * column Quantity