Explore Ā

# Custom equation functions and operators

Calculation columns in your Aha! account 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")

Top

### Numerical functions

 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.4number("13") 13number(true) 1number(false) 0

Top

### 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-11date("2018-06" + "-10") 2018-06-10date("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:dayweekmonthyear datediff("2019-01-01","2019-01-31","day") 30datediff("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 ") 2019dateformat("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:04Ztime("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

Top

### Logical functions

 Function Description Syntax ``boolean`` Use this function to force a value to be a boolean. boolean(value)boolean("True") trueboolean("False") falseboolean(1) trueboolean(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])

Top

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

Top

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

Top

### 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 false2 1 true ``<`` Use this operator to compare two numerical operands and return true if the first is less than the second. 1 < 2 true2 < 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 true2 = 1 true1 = 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 true2 <= 2 true2 <= 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" truetrue == 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" falsetrue != false true ``and`` This operator returns true if both operands are true. true and true truetrue and false true ``or`` This operator returns true if either operand is true. true or true truetrue or false truefalse or false false ``not`` This operator returns true if the operand is false. not true falsenot false true

Top