Calculate

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Calculate

Description

Performs a calculation on 1 or 2 columns and creates a new column.

 

Examples

Round a column of numbers to the nearest integer:

 

How to round a column of numbers

 

Divide a column of numbers by 100:

 

how to divide column of numbers

 

Multiply 2 columns of numeric values:

 

How to multiply 2 columns of numbers example

 

Add a days column to a date column:

 

How to add a days column to a date column example

 

Calculate the difference in days between 2 date columns:

 

How to calculate the difference in days between 2 date columns example

 

Inputs

One.

 

Options

Uncheck automatic new column name to set a name for the newly created column in New column name.

Set Operation to the operation you wish to perform.

Set Value 1 to the first column of values you wish to operate on.

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

 

Notes

The following operations are supported.

 

Operation

Value 1

Value 2

Notes

Examples

+ Add

Number

Number

Numerical addition.

1.2 + 3 = 4.2

Date

Integer number

Adds days to the date.

31/01/2021 + 7 = 07/02/2021

- Subtract

Number

Number

Numerical subtraction.

1.2 - 3 = -1.8

Date

Integer number

Subtracts days from the date.

31/01/2021 - 7 = 24/01/2021

Date

Date

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

Number

Number

Numerical multiplication.

1.2 * 3 = 3.6

/ Divide

Number

Number

Numerical division.

1.2 / 3 = 0.4

1 / 0 = Error

^ Power

Number

Number

Value 1 to the power Value 2 (Value 1Value 2).

1.2 ^ 3 = 1.728

2 ^ -3 = 0.125

% Modulus

Integer number

Integer number

Remainder after Value 1 is divided by Value 2.

7 % 2 = 1

7 % 0 = Error

Abs

Number

N/A

The absolute value.

Abs( 1.2 ) = 1.2

Abs( -1.2 ) = 1.2

And

Boolean

Boolean

Logical AND.

And( true, false ) = false

And( 1, 1 ) = true

And( 0, FALSE ) = false

Ceiling

Number

N/A

The smallest integer that is not less than the value.

Ceiling( 1 ) = 1

Ceiling( 1.2 ) = 2

Ceiling( -1.2 ) = -1

DateTimeToMSecs

ISO datetime

Integer number

Convert an ISO datetime to the number of milliseconds since 1970-01-01:00:00:00.000 UTC .

DateTimeToMSecs( 1970-01-01 ) = 0

DateTimeToMSecs( 2022-09-15T21:06:10 ) = 1663272370000

DateTimeToMSecs( 1969-12-30T13:42:23.211Z ) = -123456789

DateTimeToMSecs( 2022-09-16T13:00:02+01:00 ) = 1663329602000

DateToJulianDay

Date

N/A

Convert a date to days since the beginning of the Julian period (1st January 4713 BC).

DateToJulianDay( 31/01/2021 ) = 2459246

DateToJulianDay( 01/02/2021 ) = 2459247

DateToJulianDay( 01/01/0001 ) = 1721426

DayOfWeek

Date

N/A

Day of the week, from 1 = Monday to 7 = Sunday.

DayOfWeek( 31/01/2021 ) = 7

DayOfWeek( 01/02/2021 ) = 1

DayOfMonth

Date

N/A

Day of the month, from 1 to 31.

DayOfMonth( 31/01/2021 ) = 31

DayOfMonth( 01/02/2021 ) = 1

DayOfYear

Date

N/A

Day of the year, from 1 to 366.

DayOfYear( 01/02/2021 ) = 32

DayOfYear( 31/12/2021 ) = 365

Decrement

Number

N/A

Subtract 1.

Decrement( 1 ) = 0

Decrement( -1 ) = -2

Decrement( 1.2 ) = 0.2

Date

N/A

Subtract 1 day.

Decrement( 31/01/2021 ) = 30/01/2021

Floor

Number

N/A

The largest integer that is not greater than the value.

Floor( 1 ) = 1

Floor( 1.2 ) = 1

Floor( -1.2 ) = -2

Increment

Number

N/A

Add 1.

Increment( 1 ) = 2

Increment( -1 ) = 0

Increment( 1.2 ) = 2.2

Date

N/A

Add 1 day.

Increment( 31/01/2021 ) = 01/02/2021

IndexOf

Any

Any

The 1-based position of the first occurrence of value 1 in value 2. -1 if not found.  Case sensitive.

IndexOf( a, EasyData ) = 2

IndexOf( DATA, EasyData ) =

IndexOf( , EasyData ) = 1

IndexOf( EasyData, ) =

IndexOf( , ) = 1

JulianDayToDate

Integer number

N/A

Convert days since the beginning of the Julian period (1st January 4713 BC) to an ISO date.

JulianDayToDate( 2459246 ) = 2021-01-31

JulianDayToDate( 2459247 ) = 2021-02-01

JulianDayToDate( 1721426 ) = 0001-01-01

JulianDayToDate( 1721425 ) = Error

LastIndexOf

Any

Any

The 1-based position of the last occurrence of value 1 in value 2. -1 if not found. Case sensitive.

LastIndexOf( a, EasyData ) = 8

LastIndexOf( DATA, EasyData ) =

LastIndexOf( , EasyData ) = 9

LastIndexOf( EasyData, ) =

LastIndexOf( , ) = 1

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

LevDistance

Any

Any

Calculates the Levenshtein distance between 2 text values. Case sensitive.

LevDistance( Easy Data, easydata ) = 3

Log10

Number

N/A

The common (base 10) logarithm of the value.

Log10( 10 ) = 1

Log10( 0 ) = Error

Log2

Number

N/A

The base 2 logarithm of the value.

Log2( 10 ) = 3.3219280949

Log2( 0 ) = Error

Ln

Number

N/A

The natural (base e) logarithm of the value.

Ln( 10 ) = 2.302

Ln( 0 ) = Error

Maximum

Number

Number

Returns the larger number.

Maximum( 10, 11 ) = 11

Maximum( 10, -11 ) = 10

Date

Date

Returns the later date.

Maximum( 31/01/2021, 01/01/2022 ) = 01/01/2022

Minimum

Number

Number

Returns the smaller number.

Minimum( 10, 11 ) = 10

Minimum( 10, -11 ) = -11

Date

Date

Returns the earlier date.

Minimum( 31/01/2021, 01/01/2021 ) = 31/01/2021

MSecsToDateTime

Integer number

ISO datetime

Convert the number of milliseconds since 1970-01-01:00:00:00.000 UTC to an ISO datetime. May be affected by locale (e.g. Summertime).

MSecsToDateTime( 0 ) = 1970-01-01T00:00:00.000

MSecsToDateTime( -123456789 ) = 1969-12-30T13:42:23.211

MSecsToDateTime( 123456789 ) = 1970-01-02T10:17:36.789

Month

Date

N/A

1 = January.

Month( 31/12/2021 ) = 12

Or

Boolean

Boolean

Logical OR.

Or( true, false ) = true

Or( 1, 1 ) = true

Or( 0, FALSE ) = false

Quarter

Date

N/A

1 = Q1

Quarter( 1/1/2023 ) = 1

Quarter( 31/3/2023 ) = 1

Quarter( 1/4/2023 ) = 2

Quarter( 31/12/2023 ) = 4

Round

Number

N/A

Round to the nearest integer.

Round( 1 ) = 1

Round( -1.2 ) = -1

Round( 1.5 ) = 2

Sign

Number

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

Date

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

WordCount

Any

N/A

Count the number of words separated by whitespace and/or punctuation.

WordLength( Hello world! ) = 2

WordLength( Hello,world! ) = 2

WordLength( Hello, world! ) = 2

Xor

Boolean

Boolean

Logical XOR (exclusive OR).

Xor( true, false ) = true

Xor( 1, 1 ) = false

Xor( 0, FALSE ) = false

Year

Date

N/A

The 4 digit year.

Year( 31/01/2021 ) = 2021

 

Each row is calculated separately.

Whether values are interpreted as number, dates or text depends on Supported date formats and locale.

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 whole rows or columns use the Stats transform.

For more complex calculations, including number/date, 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.

 

See also:

Video: How to convert a Unix timestamp