Aha! Roadmaps | Custom equation functions and operators
Aha! Roadmaps 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.
Click any of the following links to skip ahead:
Functions
Operators
Functions
Aha! functions
Function | Description | Syntax |
| Use this function to reference other values in your equation. | field("Value 1") |
Numerical functions
Function | Description | Syntax |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| Use this function to present the total number of days, weeks, months, or years between dates. Valid units:
| datediff("2019-01-01","2019-01-31","day") 30 datediff("2019-01-01","2019-04-01","week") 13 |
| Use this function to present a specific date in a custom format. Available formats:
| dateformat("2019-01-01", " %Y ") 2019 dateformat("2019-01-01", "%B-%e,%y") January 1, 2019 |
| 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 |
| Use this function to present the day of the month for a specific date. | day("2019-03-08") 8 |
| Use this function to present the month of a specific date. | month("2019-03-08") = 3 |
| 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 |
| 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 |
| Use this function to present the year of a specific date. | year("2019-03-08") 2019 |
Logical functions
Function | Description | Syntax |
| 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 |
| Use this function to return the first argument that is not null. It accepts an unlimited number of arguments. | coalesce(field("field name"), 0) |
| 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 |
| Use this function to define complex if/else if/else style logic for matching values. | switch(value, match1, result1, [match2, result2, ...], [default_value]) |
Text functions
Function | Description | Syntax |
| 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" |
| Use this function to see the length of a string by character count. | length(string) length("Example") 7 |
| 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" |
| 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. If you subtract one or more dates from another, the output is in number of days. The | 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 |
| This operator returns true if both operands are true. | true and true true true and false true |
| This operator returns true if either operand is true. | true or true true true or false true false or false false |
| This operator returns true if the operand is false. | not true false not false true |