Mathematical Functions
To include data entered in another field, type in the field name between square brackets [ ]. For example, calculate BMI from existing variables [weight] in kg and [height] in cm with the following equation: [weight]*10000/([height][height]). If the field is from a different event then include the event name in square brackets before the field name.
Basic functions:
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| () | Parentheses |
Advanced functions:
| Function | Syntax | Notes / examples |
| Round | round(variable,decimal places) | If the "decimal places" parameter is not provided, it defaults to 1 dp |
| Round Up | roundup(variable,decimal places) | If the "decimal places" parameter is not provided, it defaults to 1 dp. E.g. To round up 14.384 to one decimal place: roundup(14.384,1) will yield 14.4 |
| Round Down | rounddown(variable,decimal places) | If the "decimal places" parameter is not provided, it defaults to 1 dp. E.g. To round down 14.384 to one decimal place: rounddown(14.384,1) will yield 14.3 |
| Square Root | sqrt(variable) | E.g. sqrt([height]) or sqrt(([value1]*34)/98.3) |
| Exponential | (variable)^(exponent) | Use caret ^ character and place both the number and its exponent inside parentheses: For example, (4)^(3) or ([weight]+43)^(2) |
| Absolute Value | abs(variable) | Returns the absolute value (i.e. the magnitude of a real number without regard to its sign). E.g. abs(-7.1) will return 7.1 and abs(45) will return 45. |
| Minimum | min(variable,number,...) | Returns the minimum value of a set of values in the format min([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the lowest numerical value. There is no limit to the amount of numbers used in this function. |
| Maximum | max(variable,number,...) | Returns the maximum value of a set of values in the format max([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the highest numerical value. There is no limit to the amount of numbers used in this function. |
| Mean | mean(variable,number,...) | Returns the mean (i.e. average) value of a set of values in the format mean([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the mean value computed from all numerical, non-blank values. There is no limit to the amount of numbers used in this function. |
| Median | median(variable,number,...) | Returns the median value of a set of values in the format median([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the median value computed from all numerical, non-blank values. There is no limit to the amount of numbers used in this function. |
| Sum | sum(variable,number,...) | Returns the sum total of a set of values in the format sum([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the sum total computed from all numerical, non-blank values. There is no limit to the amount of numbers used in this function. |
| Standard Deviation | stdev(variable,number,...) | Returns the standard deviation of a set of values in the format stdev([num1],[num2],[num3],...). NOTE: All blank values will be ignored and thus will only return the standard deviation computed from all numerical, non-blank values. There is no limit to the amount of numbers used in this function. |
| Log | log(variable) | Returns the natural log |
Note: variable can be a calculation placed in brackets.
Date and Time Functions
Calculate the difference between two dates or times by using the datediff function: datediff([date1], [date2], "units", "dateformat", returnSignedValue)
units
| "y" | years | 1 year = 365.2425 days |
| "M" | months | 1 month = 30.44 days |
| "d" | days | |
| "h" | hours | |
| "m" | minutes | |
| "s" | seconds |
dateformat: If the dateformat is not provided, it will default to "ymd"; both dates MUST be in the format specified in order to work.
| "ymd" | Y-M-D (default) |
| "mdy" | M-D-Y |
| "dmy" | D-M-Y |
returnSignedValue: The parameter returnSignedValue denotes the result to be signed or unsigned (absolute value), in which the default value is "false", which returns the absolute value of the difference. For example, if [date1] is larger than [date2], then the result will be negative if returnSignedValue is set to true. If returnSignedValue is not set or is set to false, then the result will ALWAYS be a positive number. If returnSignedValue is set to false or not set, then the order of the dates in the equation does not matter because the resulting value will always be positive (although the + sign is not displayed but implied).
| false | (default) |
| true |
Examples:
| datediff([dob],[date_enrolled],"d") | Yields the number of days between the dates for the date_enrolled and dob fields, which must be in Y-M-D format |
| datediff([dob],"05-31-2007","h","mdy",true) | Yields the number of hours between May 31, 2007, and the date for the dob field, which must be in M-D-Y format. Because returnSignedValue is set to true, the value will be negative if the dob field value is more recent than May 31, 2007. |
Conditional Logic
Conditional logic can only process numeric field values and only output numbers. Conditional statements must be placed within parentheses.
E.g., if((conditional statement),true value, wrong value)
True and wrong values can incorporate further conditional logic.
| <> | not |