<< Click to Display Table of Contents >> Navigation: Reference > Transforms > Calculate |
Performs a calculation on 1 or 2 columns and creates a new column.
Round a column of numbers to the nearest integer:
Divide a column of numbers by 100:
Multiply 2 columns of numeric values:
Add a days column to a date column:
Calculate the difference in days between 2 date columns:
One.
•Set Value 1 to the first column of values you wish to operate on.
•Set Operation to the operation you wish to perform.
•Set Value 2 type depending on whether you wish to use a single value or a column of values for the second value (binary operations only).
•Set Value 2 to the second column of values or constant value you wish to operate on (binary operations only).
•The following operations are supported.
Operation |
Value 1 |
Value 2 |
Notes |
Examples |
---|---|---|---|---|
+ Add |
Numerical addition. |
1.2 + 3 = 4.2 |
||
Adds days to the date. |
31/01/2021 + 7 = 07/02/2021 |
|||
- Subtract |
Numerical subtraction. |
1.2 - 3 = -1.8 |
||
Subtracts days from the date. |
31/01/2021 - 7 = 24/01/2021 |
|||
Days the date in Value 1 is after the date in Value 2 (negative if before). |
31/01/2021 - 24/01/2021 = 7 24/01/2021 - 31/01/2021 = -7 |
|||
* Multiply |
Numerical multiplication. |
1.2 * 3 = 3.6 |
||
/ Divide |
Numerical division. |
1.2 / 3 = 0.4 1 / 0 = Error |
||
^ Power |
Column 1 to the power Column 2 (col1col2). |
1.2 ^ 3 = 1.728 2 ^ -3 = 0.125 |
||
% Modulus |
Remainder after Value 1 is divided by Value 2. |
7 % 2 = 1 7 % 0 = Error |
||
Abs |
N/A |
The absolute value. |
Abs( 1.2 ) = 1.2 Abs( -1.2 ) = 1.2 |
|
Ceiling |
N/A |
The smallest integer that is not less than the value. |
Ceiling( 1 ) = 1 Ceiling( 1.2 ) = 2 Ceiling( -1.2 ) = -1 |
|
DayOfWeek |
N/A |
Day of the week, from 1 = Monday to 7 = Sunday. |
DayOfWeek( 31/01/2021 ) = 7 DayOfWeek( 01/02/2021 ) = 1 |
|
DayOfMonth |
N/A |
Day of the month, from 1 to 31. |
DayOfMonth( 31/01/2021 ) = 31 DayOfMonth( 01/02/2021 ) = 1 |
|
DayOfYear |
N/A |
Day of the year, from 1 to 366. |
DayOfYear( 01/02/2021 ) = 32 DayOfYear( 31/12/2021 ) = 365 |
|
Decrement |
N/A |
Subtract 1. |
Decrement( 1 ) = 0 Decrement( -1 ) = -2 Decrement( 1.2 ) = 0.2 |
|
N/A |
Subtract 1 day. |
Decrement( 31/01/2021 ) = 30/01/2021 |
||
Floor |
N/A |
The largest integer that is not greater than the value. |
Floor( 1 ) = 1 Floor( 1.2 ) = 1 Floor( -1.2 ) = -2 |
|
Increment |
N/A |
Add 1. |
Increment( 1 ) = 2 Increment( -1 ) = 0 Increment( 1.2 ) = 2.2 |
|
N/A |
Add 1 day. |
Increment( 31/01/2021 ) = 01/02/2021 |
||
JulianDay |
N/A |
Days since the beginning of the Julian period. |
JulianDay( 31/01/2021 ) = 2459246 JulianDay( 01/02/2021 ) = 2459247 |
|
Length |
Any |
N/A |
Then number of characters in the value (include any whitespace). |
Length( 1.2 ) = 3 Length( 31/01/2021 ) = 10 Length( abcd ) = 4 |
Ln |
N/A |
The natural (base e) logarithm of the value. |
Ln ( 10 ) = 2.302 Ln ( 0 ) = Error |
|
Log10 |
N/A |
The common (base 10) logarithm of the value. |
Log10 ( 10 ) = 1 Log10 ( 0 ) = Error |
|
Month |
N/A |
1 = January. |
Month( 31/12/2021 ) = 12 |
|
Round |
N/A |
Round to the nearest integer. |
Round( 1 ) = 1 Round( -1.2 ) = -1 Round( 1.5 ) = 2 |
|
Sign |
N/A |
1 if value > 0, 0 if value = 0, -1 of value < 0. |
Sign( 1.2 ) = 1 Sign( 0 ) = 0 Sign( -1.2 ) = -1 |
|
WeekOfYear |
N/A |
The year and the week of the year, from 01 to 53, separated with a comma. In accordance with ISO 8601, each week falls in the year to which most of its days belong, in the Gregorian calendar. As ISO 8601's week starts on Monday, this is the year in which the week's Thursday falls. |
WeekOfYear( 31/12/2020 ) = 2020,53 WeekOfYear( 06/01/2021 ) = 2021,01 WeekOfYear( 31/12/2021 ) = 2021,52 |
|
Year |
N/A |
The 4 digit year. |
Year( 31/01/2021 ) = 2021 |
•Whether values are interpreted as number, dates or text depends on Supported date formats and Locale in the Preferences window.
•To concatenate text in different columns use the Concat Cols transform.
•To compare 2 columns use the Compare Cols transform.
•For logical operations use the If transform.
•For operations on rows or columns use the Stats transform.
•For more complex number/date calculations use the JavaScript transform.
•To modify the numerical precision of the results use the Num Format transform.
•To add a new column of values use the New Col transform.