Custom equation functions and operators

Function

Description

Syntax

field

Use this function to reference other values in your equation.

field("Value 1")

Function

Description

Syntax

avg

Use this function to present the average value of a data set.

avg(field("field name"), field("field name 2")...)

avg(1, 2, 3) 2

max

Use this function to present the maximum value in a data set.

max(field("field name"), field("field name 2")...)

max(1, 2, 3) 3

min

Use this function to present the minimum value in a data set.

min(field("field name"), field("field name 2")...)

min(1, 2, 3) 1

number

Use this function to force a value to be a number.

number(value)

number(field("field name")

number(1.4) 1.4

number("13") 13

number(true) 1

number(false) 0

Function

Description

Syntax

date

Use this function to force a value to be a date.

date(value)

date(field("field name")

date(1528693264) 2018-06-11

date("2018-06" + "-10") 2018-06-10

date("2018-04-21T01:02:03") 2018-04-21

datediff

Use this function to present the total number of days, weeks, months, or years between dates.

Valid units:

• day

• week

• month

• year

datediff("2019-01-01","2019-01-31","day") 30

datediff("2019-01-01","2019-04-01","week") 13

dateformat

Use this function to present a specific date in a custom format.

Available formats:

• %Y - Year with century

• %y - Year without century

• %m - Month of the year, zero-padded

• %_m - Month of the year, blank-padded

• %-m - Month of the year, no-padded

• %B - Month, the full name

• %b - The abbreviated month name

• %d - Day of the month, zero-padded

• %-d - Day of the month, no-padded

• %e - Day of the month, blank-padded

dateformat("2019-01-01", " %Y ") 2019

dateformat("2019-01-01", "%B-%e,%y") January 1, 2019

today

Use this function to present the current date.

The today function is only available in calculation columns. It is not available in custom worksheets.

today() 2019-03-11

day

Use this function to present the day of the month for a specific date.

day("2019-03-08") 8

month

Use this function to present the month of a specific date.

month("2019-03-08") = 3

quarter

Use this function to present the quarter of a specific date.

The quarter for a given date is impacted by the Fiscal year start setting in Aha! Roadmaps Settings ⚙️ Account Profile.

quarter("2019-03-08") 1

time

Use this function to force a value to be a time.

time(value)

time(field("field name")

time(1528693264) 2018-06-11T05:01:04Z

time("2018-04-21") 2018-04-21T00:00:00Z

year

Use this function to present the year of a specific date.

year("2019-03-08") 2019

Function

Description

Syntax

boolean

Use this function to force a value to be a boolean.

boolean(value)

boolean("True") true

boolean("False") false

boolean(1) true

boolean(0) false

coalesce

Use this function to return the first argument that is not null. It accepts an unlimited number of arguments.

coalesce(field("field name"), 0)

if

Use this function to evaluate a condition and present one value if the condition is true or another value if false.

if(condition, true_value, false_value)

if(2 1, "Green", "Red") "Green"

if(false, 1, -1) -1

switch

Use this function to define complex if/else if/else style logic for matching values.

switch(value, match1, result1, [match2, result2, ...], [default_value])

Function

Description

Syntax

string

Use this function to force a value to be a string.

string(value)

string(1.4) "1.4"

string(1243) "1243"

string(true) "True"

string(false) "False"

length

Use this function to see the length of a string by character count.

length(string)

length("Example") 7

substr

Use this function to extract a portion of a string, by index and character count. Negative start and count values are not supported.

substr(string, start, count)

substr("Example", 1, 2) "xa"

strpos

Use this function to find the index of a substring in a string (a string within a string). Returns -1 when no match is found.

strpos(string, search)

strpos("Example", "amp") 2

Operator

Description

Examples

+

Use this operator to add two numerical operands, concatenate two strings, or add an offset to a time or date.

12 + 44.5 56.5

" 2019-03-12 " + 10 '2019-03-22'

"first" + " " + "last" "first last"

-

Use this operator to subtract two numerical operands or to subtract an offset from a time or date.

If you subtract one or more dates from another, the output is in number of days. The datediff function provides a simpler way to do this.

44.5 - 12 32.5

"2019-03-12" - 10 "2019-03-02"

"2019-03-12" - "2019-03-02" "10"

*

Use this operator to multiply two numerical operands.

44.5 * 11 489.5

/

Use this operator to divide two numerical operands.

44 / 16 2.75

Operator

Description

Examples

>

Use this operator to compare two numerical operands and return true if the first is greater than the second.

1 2 false

2 1 true

<

Use this operator to compare two numerical operands and return true if the first is less than the second.

1 < 2 true

2 < 1 false

>=

Use this operator to compare two numerical operands and return true if the first is greater than or equal to the second.

2 = 2 true

2 = 1 true

1 = 2 false

<=

Use this operator to compare two numerical operands and return true if the first is less than or equal to the second.

1 <= 2 true

2 <= 2 true

2 <= 1 false

==

Use this operator to compare two numerical, boolean, strings, dates, or times and return true if the operands are equal.

1 == 1 true

"lorem" == "ipsum" false

"2018-05-09" == "2018-05-09" true

true == false false

!=

Use this operator to compare two numerical, boolean, strings, dates, or times and return true if the operands are not equal.

1 != 1 false

"lorem" != "ipsum" true

"2018-05-09" != "2018-05-09" false

true != false true

and

This operator returns true if both operands are true.

true and true true

true and false true

or

This operator returns true if either operand is true.

true or true true

true or false true

false or false false

not

This operator returns true if the operand is false.

not true false

not false true