We have added a set of convenience and compatibility functions for use in formulas of calculated variables:
| IIF(expr, n1, n2) | returns n1 if expr evaluates to true, otherwise returns n2 |
| TRUNC(n) | Short for truncate |
| ISNAN(n) | returns TRUE if n is not a number (f.e. through division by zero) |
| INLIST(n1, n2[, ...]) | Returns the index of the first parameter in the list of other parameters, returns 0 (zero) if not found. This function requires at least 2 parameters. |
| TRIM(s) | Returns the string s with all whitespace characters removed from the start and end. |
| TEXTVALUE(s) | Returns the numeric value of represented by string s or NULL if s cannot be converted to a numeric value. |
| NOTMISSING(n[,..]) | Returns the first parameter that does not equal NULL, this function can take any number of parameters but requires at least 1. |
| LIKE(expr1, n1[,...]) | Returns value N of the first expr that evaluates to true. This function can take any even number of parameters. |
| LABEL(expr1, n1[,...]) | See LIKE |
| CHOOSE(expr1, n1 [,...]) | Returns N based on the index specified in expr1, so if expr1 evaluates to 1 returns n1, if expr1 evaluates to 2 returns n2 etc. This function requires a minimum of two parameters, if expr1 does not evaluate to a number or a valid index NULL is returned. |
| AVG(n1, n2 [,...]) | returns the average of the given numbers, NULL values are ignored. If no values found NULL is returned |
| YEAR(d) | Returns the year part of the given date |
| MONTH(d) | Returns the month part of the given date |
| DAY(d) | Returns the day part of the given date |
| HOUR(d) | Returns the hour part of the given date |
| MINUTE(d) | Returns the minute part of the given date |
| SECONDS(d) | Returns the seconds part of the given date |
| DAYOFWEEK(d) | Returns the day of the week for the given date |
| WEEK(d) | Returns the week number for the given date, the week number is calculated using the FirstFourDayWeek rule and taking Monday as the start of the day. |
| NOW() | Returns the current date time |
| PARSEDATE(s [,s]) | Returns a date value resulting from parsing the first string parameter, optionally taking into account the dateformat string as specified in the second parameter. The second parameter can hold multiple formats separated by a pipe character '|'. Date format strings are explained here |
| DATEFORMAT(d, s) | Converts DateTime value D to a string using the format specified in string S. Date format strings are explained here |
| YEARMONTHNO(d) | Converts datetime value D to an integer value containing only the year and month as YYYYMM. |
| YEARMONTH(d [,s]) | Converts datetime value D to a string in the format "YYYY MM" by default, an optional format string can be provided in which '{0}' is replaced with the year and '{1}' is replaced with the month. An example could be "Year: {0}, Month: {1}" |
| YEARQUARTERNO(d) | Converts the datetime value d to an integer number based on the year and the quarter number as YYYYQ |
| YEARQUARTER(d [,s]) | Converts datetime value D to a string in the format "YYYY Q" by default, an optional format string can be provided in which '{0}' is replaced with the year and '{1}' is replaced with the quarter. An example could be "Year: {0}, Quarter: {1}" |
| YEARWEEKNO(d) | Converts the datetime value D to an integer number based on the year and week in the format YYYYWW. |
| YEARWEEK(d [,s]) | Converts datetime value D to a string in the format "YYYYWW" by default, an optional format string can be provided in which '{0}' is replaced with the year and '{1}' is replaced with the week. An example could be "Year: {0}, Week: {1}". The weeknumber is calculated using the FirstFourDayWeek rule and having Monday as a start of the week day. |
| LIKE(s, s) | Compares the first string parameter to the second and returns TRUE if they are found to be equal. The second parameter can be a pattern using the following special characters:
|