How to use Calculated Fields
- Theresa Baker (Unlicensed)
- Madison Lethebe
- Mandavi Kashyap
What are calculated fields?
REDCap has the ability to make real-time calculations on data entry forms and surveys.
How do I format calculated fields?
In order for the calculated field to function, it will need to be formatted in a particular way. This is somewhat similar to constructing equations in Excel or with certain scientific calculators. The variable names/field names can be used as variables in the equation, but you must place [ ] brackets around each variable. Please be sure that you follow the mathematical order of operations when constructing the equation or else your calculated results might end up being incorrect.
Calculations in REDCap are formatted the same way as Excel, but instead of using the cell names ([A2]+[A3]), the variable names are used ([bpi_q1]+[bpi_q2]).
How do I create a calculated field?
On the Online Designer page, you would select Calculated Field as the field type. Type the calculation into the Calculation Equation box. If you have test data in the project, you can test the calculation to see if it’s valid by selecting a study ID from the Test calculation with a record dropdown list.
Whatmathematicaloperationsareavailablefor calculated fields?
| + | Add |
| _ | Subtract |
| * | Multiply |
| / | Divide |
Null or blank values can be referenced using””or “NaN”
FAQ
Can I use conditional logic in a calculated field?
Yes, you may use conditional logic (i.e. an IF/THEN/ELSE statement) by using the function: if(CONDITION, value if condition is TRUE, value if condition is FALSE)
This construction is similar to “IF” statements in Excel. Provide the condition first (e.g. [weight] = 3), then give the resulting value if it is true, and lastly give the resulting value if the condition is false.
if([weight] > 100, 33, 11)
In this example, if the value of the field 'weight' is greater than 100, then it will give a value of 33, but if 'weight' is less than or equal to 100, it will give 11 as the result.
Common calculations:
BMI Calculation:
[weight]*10000/([height]*[height]) = units in kilograms and centimeters
([weight]/([height]*[height]))*703 = units in pounds and inches
Note: ‘weight’ and ‘height’ need to be field names in your project. If you called these fields something else, use the names you called those fields instead of the ones listed above.
Calculate the difference between two dates or datetimes (i.e., age at enrollment based upon DOB and date of enrollment, length of hospital stay based on admit and discharge dates):
datediff([date1],[date2], "units", "date format", Return Signed Value)
| Units | ||
| "y" | years | 1 year = 365.2425 days |
| "M" | months | 1 month = 30.44 days |
| "d" | days | |
| "h" | hours | |
| "m" | minutes | |
| "s" | seconds |
| Date Format | |
| "ymd" | Y-M-D (default) |
| "mdy" | M-D-Y |
| "dmy" | D-M-Y |
- If the date format is not provided, it will default to "ymd".
- Both dates MUST be in the format specified in order to work.
| Return Signed Value | |
false (default) | Difference in date will be the absolute value, in other words, it will always be a positive value |
| true | Difference in the values returns a positive or negative result. If date 1 is larger than date 2, then the returned result will be negative |
EXAMPLES of calculated fields for difference in dates
| datediff([dob],[date_enrolled],"y") | Yields the number of years between the dates for the date_enrolled and dob fields, which must be in Y-M-D format |
| datediff([dob],"05-31-2017","h","mdy",true) | Yields the number of hours between May 31, 2017, 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, 2017. |
| datediff([dob],[visit_date], “y”, “mdy”) | Yields the number of years between the dates for the dob and visit_date fields, in the M-D-Y format. |
| datediff("today",[screen_date],"m") | Yields the number of months since the dates for the screen_date and today’s date. NOTE: The "today" variable can ONLY be used with date fields and NOT with time, datetime, or datetime_seconds fields. It is strongly recommended, HOWEVER, that you do not use "today" in calculated fields. This is because every time you access and save the form, the calculation will run. For example, if you calculate the age as of today, then a year later you access the form to review or make updates, the elapsed time as of "today" will also be updated (+1 yr). Most users calculate time off of another field (e.g. screening date, enrollment date). |