In this article
When using the Expression Builder, you have access to descriptions of all the functions available. Click the Click here for documentation... button to open a new window containing the information.
The page contains a considerable amount of information, so it is divided into separate sections, for each main type of function. You can close down individual sections as required to reduce the amount of information presented. Click the "up-arrow" button in the title bar for a section to close that section. The button then changes to "down-arrow"; click that button to expand the section again.
Math Functions
| Function | Return type | Description |
| Abs(double d) | double | Absolute value. |
| Average(double d1, double d2, ...) | double | Returns the average (arithmetic mean) of the arguments. |
| Ceil(double d) | double | The smallest integer greater than or equal to the specified number. |
| Floor(double d) | double | The greatest integer less than or equal to the specified number. |
| Round(double d, [int decimals]) | double | Returns the number with the specified precision nearest the specified value. Parameters d - A number to be rounded. decimals - The number of significant fractional digits (precision) in the return value. Ranges from 0 to 28. Return Value The number nearest d with precision equal to decimals. If d is halfway between two numbers, one of which is even and the other odd, then the even number is returned. If the precision of d is less than decimals, then d is returned unchanged. Remarks The decimals parameter specifies the number of significant fractional digits in the return value and ranges from 0 to 28. If decimals is zero, then a whole number is returned. |
Logical Functions
| Function | Return type | Description |
| IIF(bool condition, expression1, expression2) IF(bool condition, expression1, expression2) | object | If condition evaluates to TRUE, expression1 is returned. If condition evaluates to FALSE, expression2 is returned. This function accepts any type for its second and third parameters as long as they are both of the same type. |
| Any(string questionID, string code, ...) | boolean | Returns true if respondent's answer has one of the values (equal to code). |
| All(string questionID, string code, ...) | boolean | Returns true if respondent's answer to the question contains all the selected codes. |
| None(string questionID, string code, ...) | boolean | Returns true if respondent's answer to the question does not contain all selected values. |
| Answered(string questionId) | boolean | Determines whether or not a respondent answered the selected question. |
| IsNull(string questionId) | boolean | Determines whether or not a respondent's answer is equal to null. |
| Between(double TestValue, double LowValue, double HighValue) | boolean | Determines whether the value of an expression lies between the values of two other expressions. |
Text Functions
| Function | Return type | Description |
| Left(string, int nCount) | string | Returns the first (leftmost) nCount characters from a string. |
| Right(string, int nCount) | string | Returns the last (rightmost) nCount characters from a string. |
| Len(string Text) | int | Returns the number of characters in the string. |
| Lower(string Text) | string | Converts all characters in a text string to lowercase. |
| Substring(string Text, int nFirst[, int nCount]) | string | Returns a substring of length nCount characters from a string, starting at position nFirst (zero-based). If nCountequals 0 - will return string form the nFirst till end. |
| Substitute(string text, string oldText, string newText, bool isCaseSensitive) | string | Substitutes newText for oldText in a text string with case sensitive or not. |
| Trim(string Text) | string | Removes all occurrences of white space characters from the beginning and end of a string. |
| Upper(string Text) | string | Converts all characters in a text string to uppercase. |
| StartsWith(string Text, string startWithText) | boolean | Determines whether or not a text from the first parameter starts with the parameter value. |
| EndsWith(string Text, string endsWithText) | boolean | Determines whether or not a text from the first parameter ends with the parameter value. |
| Contains(string text, string innerText) | boolean | Determines whether or not a text from the first parameter contains the parameter value. |
Date Functions
| Function | Return type | Description |
| Now() | DateTime | Returns the current date and time according to the setting of your computer's system date and time. |
| DATEADD(string interval, double number, DateTime date) | DateTime | Number from 0 to 59, inclusive, representing the minute of the hour. yyyy - Year q - Quarter m - Month y - Day of year (same as Day) d - Day w - Weekday (same as Day) ww - Week h - Hour n - Minute s - Second Examples: // add 1 month to 31-jan-10 DateAdd("m", 1, "31-Jan-10") // add one quarter to todays date DateAdd("q", 1, Now())
|
| DATEDIFF(string interval, DateTime date1, DateTime date2) | int | Returns the number of intervals between two dates. Parameters interval - DateInterval enumeration value or string expression representing the time interval you want to use as the unit of difference between date1 and date2. The interval argument can have one of the settings listed here. date1, date2 - Date expressions. Two dates you want to use in the calculation. Remarks If Date1 represents a later date and time than Date2, DATEDIFF returns a negative number. |
| DATEPART(string interval, DateTime date) | int | Returns an Integer value containing the specified component of a given Date value. Parameters interval - DateInterval enumeration value or string expression representing the part of the date/time value you want to return. The interval argument can have one of the settings listed here. date - Date expression you want to evaluate. |
| DATE(int year, int month, int day [, int hour, int minute, int second]) | DateTime | A Date value representing a specified year, month, day, hour, minute, and second. Parameters year - The year (1 through 9999). month - The month (1 through 12). day - The day (1 through the number of days in month). hour - Optional. The hours (0 through 23). minute - Optional. The minutes (0 through 59). second - Optional. The seconds (0 through 59). |
Date Intervals
| Constant | Numeric value | String expression |
| dtYear | 0 | yyyy |
| dtQuarter | 1 | q |
| dtMonth | 2 | m |
| dtDayOfYaear | 3 | y |
| dtDay | 4 | d |
| dtWeekday | 6 | w |
| dtHour | 7 | h |
| dtMinute | 8 | n |
| dtSecond | 9 | s |
Conversion Functions
| Function | Return type | Description |
| ToDate(string Text) | DateTime | Create a DateTime from string. |
| ToInt(object Obj) | int | Convert object to integer. |
| ToFloat(object Obj) | float | Convert object to float. |
| ToDouble(object Obj) | double | Convert object to double. |
| ToString(object Obj) | string | Convert object to string. |
| ToBoolean(object Obj) | boolean | Convert object to boolean value. |
Different Functions
| Function | Return type | Description |
| Weights(string questionId, ...) | int | Returns the weights of answers of questions. |
| GetLookupId(string key, int schemaId, int relationId) | int | Returns a LookupId. |
| GetLookupLabel(string key, int schemaId, int relationId) | string | Returns a LookupLabel. |