This section includes a number of commonly used EDC formulas.

The ‘Average’ formula is used to average all responses for any given question. Formula parameters include:

1. Question Name

2. Precision of Calculation (optional, but required if a third parameter is added)

3. Type of Average (optional, but required if more parameters are added)

4. Based on the option selected in 3rd parameter (may or may not be required)

5. Based on the option selected in 3rd parameter (may or may not be required)

Note: If the 3rd parameter is part of the function call, then the corresponding parameters corresponding to that option become necessary.

Example:

The ‘Iff’ formula is used to evaluate a certain condition and then further evaluate a formula based on how the condition is evaluated. Parameters include:

The “Iff” formula is used to evaluate a condition as TRUE/FALSE. Based on that evaluation, a value is assigned to the Calculated Field. Parameters include:

Iff({Condition to check},"Value if TRUE","Value if FALSE")
Parameter 1. {Condition to check}
Parameter 2. "Value if TRUE"
Parameter 3. "Value if FALSE"

1. Condition to check - a boolean equation that should result in a value of TRUE or FALSE

2. Value if TRUE - the value the Calculated Field Question will be if the Condition to check is TRUE

3. Value if FALSE - the value the Calculated Field Question will be if the Condition to check is FALSE

Examples

• Iff((14 - 7) > 0, DateDiff('today', ‘yesterday', ‘h'), 50 / 2)

  • The “Condition to check” is “(14 - 7) > 0”

  • The “Value if TRUE” is “DateDiff(’today’, ‘yesterday’, 'h')” which is 24

  • The “Value if FALSE” is “50 / 2” which is 25

  • Since (14 - 7) > 0 is TRUE, the value assigned to the Calculated Field Question will be 24
    

• Iff([RadioQ1] > 0, [RadioQ1] < 3, FALSE)

  • The “Condition to check” is “[RadioQ1] > 0”

  • The “Value if TRUE” is “[RadioQ1] < 3” which will be TRUE/FALSE depending on the value of [RadioQ1]

  • The “Value if FALSE” is “FALSE”

  • The table below will show what the Calculated Field Question value will be based on the value of [RadioQ1]

• Iff(Contains([QuestionOne], 2), 1, 0)

  • The “Condition to check” is “Contains([QuestionOne],2)”

  • The “Value if TRUE” is 1

  • The “Value if FALSE” is 0

  • If the participant included option value 2 in checkbox question [QuestionOne], then the “Condition to check” equates to TRUE and the “Value if TRUE” will be assigned to the Calculated Field Question, which is 1.

  • If the participant didn’t include option value 2 in checkbox question [QuestionOne], then the “Condition to check” equates to FALSE and the “Value if FALSE” will be assigned to the Calculated Field Question, which is 0.

The ‘DateDiff’ formula is used to evaluate the difference between two Dates or two Times. Parameters include:

1. The Date or Time from which the other date will be subtracted from.

2. The Date or Time that will be subtracted from the first parameter.
   For the above two parameters, the date can be optionally specified as the following strings:
   'now' → Date and time at the moment logic is evaluated
   'today' → Date on which the formula is evaluated
   'yesterday' → Yesterday’s date (no time component)
   'tomorrow' → Tomorrow’s date (no time component)

3. The format in which the result is required.
   The format can be:
   'd' → total days
   'h' → total hours
   'm' → total minutes
   's' → total seconds

Examples:

• DateDiff('yesterday', ‘now', ‘m')
  This calculates the total minutes between yesterday’s date (parameter 1) and the date and time for right now (parameter 2).
  If yesterday's date is 07/10/2023 and we are evaluating this function for 07/11/2023 at 4:07p.m. then the results (in minutes) should look like: ‘yesterday’ (Parameter 1) minus ‘now’ (Parameter 2) = -2407.5039294216667

Using DateDiff to change stages:

• When do I use DateDiff to change stages?

  • DateDiff can be used when you want to change to the next stage after a certain amount of time has elapsed and after the date question has been answered.

The following scenario can be interpreted as such:

The Formula Based “StartStudy” stage contains an assessment that includes a radio question type named “nextstage” and when answered with “yes”, it will transition to the “Baseline” stage.

The Formula Based “Baseline” stage contains a baseline assessment that includes a radio question type named “pmpiq23” and when answered (since all responses are greater than 0) will transition to the “FinalStage”. However, if the baseline assessment is not answered within 3 days (as shown in the example below), then the stage transition from “Baseline” to “FinalStage” will still occur.

The “FinalStage” stage is Time Based and will only be active for 1 day (24 hours) since the stage does not end at midnight.

The ‘Contains’ formula is a TRUE/FALSE statement used to check if a particular option was selected in a checkbox question type. In CMS (platform v1), the way to do that is to write something like this: [checkboxq(value)] == 1. This has been changed up in V2. The new formula to check if a checkbox contains a specific value is:

Contains([questionName], 2)

This formula accepts two parameters:

1. The variable/question name

2. The value to check

Examples:

• Contains([CheckboxQ1], 3)
  The formula will get the last response(s) for the question “CheckboxQ1” and check if ‘3' was one of them. If ‘3’ was one of them the logic will return TRUE and if '3’ was not one of them the logic will return FALSE.

Checkbox syntax from CMS is now supported. [CheckboxQuestion(1)] will result in 0 if the option with value “1” was not selected by participant and vice versa. This syntax doesn’t support default value format such as [RadioQuestion:-999] (which will result in a default value of -999 if no value was found for the question “RadioQuestion”]

This formula is used to check if a particular question has been recorded in the Database with a response or not. This formula accepts one parameter which is the question name. The formula returns true if a response was found and false if a response was not found.

Example

• ResponseExists([RadioQ])

• Exists([RadioQ])

Automatically returns the most recent response value when the variable name is put within brackets [ ] .

Default values (applied when a response for a question cannot be found) can be defined in the declaration of question name followed by a colon “:”.

Examples:

• [colorblue:-1] → Value assumed for the variable “colorblue” is -1 if no response found in the database.

• [study_startdate:2100-01-01] → Value assumed for the variable “study_startdate” is 2100-01-01 if not value found.

• [RadioQ1:0] → If no response found, the value for “RadioQ1” is assumed to be 0.