##### Page tree
Go to start of banner

# Calculated Fields

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

 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

• No labels