3.5.7

Getting started

ACCOUNTS

Data pipelines

Administration

Advanced

calculate

Overview

The

`calculate`

statement is used to perform arithmetic operations using literal and column values.Syntax

`calculate column`

`ResultCol`

`as`

`source operation source`

where *source* is either of

`column`

`colName`

or `value`

`literal_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`

Details

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
`value`

where`N`

*N*is the literal number required - A column name is specified using
`column`

where`colName`

*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.

Additional notes:

- 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.

Examples

- 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`

Last modified 2yr ago

Export as PDF

Copy link