Custom equation functions and operators

Aha! custom worksheets and calculation columns support many of the functions and operators you are used to in your preferred spreadsheet application. These functions and operators can be used in equations to calculate numbers and dates. For example, you can track a feature's cost through development or the number of remaining days until the feature launches.  

Functions

Operators

 

Functions

Aha! functions 

Function Description Syntax
field
Use this function to reference other values in your equation.

field("Value 1")

Numerical functions 

Function Description Syntax
avg
Use this function to present the average value of a data set.

avg(1, 2, 3)
=> 2

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

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

max(1, 2, 3)
=> 3

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

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

min(1, 2, 3)
=> 1

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

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

Date functions 

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.

Note: 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 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

Logical functions

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

 

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 

Text functions

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

 

Operators

Numerical operators

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.

Note: 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

Logical operators

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

 

 


Was this article helpful?
0 out of 0 found this helpful