This section includes a number of commonly used EDC formulas.

The average formula is used to average all values for any given question. In the event of no response given for a question, it is not included in the average calculation.

Average({Variable Name}, "Decimal point precision", "Average Type", 
      "Varied parameter based on Average Type", "Varied parameter based on Average Type")
Parameter 1. {Variable Name}
Parameter 2. "Decimal point precision"
Parameter 3. "Average Type"
Parameter 4 & 5. "Varied parameter based on Average Type"

1. {Variable Name} - required parameter to find and average values

2. “Decimal point precision” - optional, but required if an average type is specified

3. “Average Type” - optional; explained below

4. “Varied parameter based on Average Type” - required based on average type parameter

Average Types

Below are explanations of each average type. The decimal point precision (parameter 2) will be 3, but it can be any number needed for your study.

1. Average all response values

   1. Average([VariableName])

   2. Average([VariableName], 3)

   3. Average([VariableName], 3, 1)

      1. If using example a, the decimal point precision is defaulted to 2.

2. Average last n days

   1. Average([VariableName], 3, 2, n)

      1. Parameter 4 specifies n number of days

3. Average n days starting from a specific date

   1. Average([VariableName], 3, 3, n, ‘Date’)

      1. Parameter 4 specifies n number of days

      2. Parameter 5 specifies a start date. This can be in ‘YYYY-MM-DD’ notation or a Date question.

4. Average n days ending on a specific date

   1. Average([VariableName], 3, 4, n, ‘Date’)

      1. Parameter 4 specifies n number of days

      2. Parameter 5 specifies an end date. This can be in ‘YYYY-MM-DD’ notation or a Date question. The responses recorded on this date are EXCLUDED from the calculation.

5. Average last n responses

   1. Average([VariableName], 3, 5, n)

      1. Parameter 4 specifies n number of responses

6. Average n responses starting from a specific date

   1. Average([VariableName], 3, 6, n, ‘Date’)

      1. Parameter 4 specifies n number of responses

      2. Parameter 5 specifies a start date. This can be in ‘YYYY-MM-DD’ notation or a Date question.

7. Average n responses ending on a specific date

   1. Average([VariableName], 3, 7, n, ‘Date’)

      1. Parameter 4 specifies n number of responses

      2. Parameter 5 specifies an end date. This can be in ‘YYYY-MM-DD’ notation or a Date question. The responses recorded on this date are EXCLUDED from the calculation.

8. Average since a specific date

   1. Average([VariableName], 3, 8, ‘Date’)

      1. Parameter 4 specifies a start date. This can be in ‘YYYY-MM-DD’ notation or a Date question.

9. Average until a specific date

   1. Average([VariableName], 3, 9, ‘Date’)

      1. Parameter 4 specifies an end date. This can be in ‘YYYY-MM-DD’ notation or a Date question. The responses recorded on this date are EXCLUDED from the calculation.

10. Average between a specified start and end date

    1. Average([VariableName], 3, 10, ‘Start Date’, ‘End Date’)

       1. Parameter 4 specifies a start date. This can be in ‘YYYY-MM-DD’ notation or a Date question.

       2. Parameter 5 specifies an end date. This can be in ‘YYYY-MM-DD’ notation or a Date question. The responses recorded on this date are EXCLUDED from the calculation.

Suppose there’s a study that wants to check a participant’s average cigarette use after 20 days. The following table contains responses recorded in the dropdown question [CigarettesSmoked] over 20 days.

[CigarettesSmoked] = Dropdown question

How many cigarettes did you smoke?

0-10+

Average Type 1: Average all response values

1. Average([CigarettesSmoked]) = 2.92

2. Average([CigarettesSmoked], 5) = 2.9157

3. Average([CigarettesSmoked], 3, 1) = 2.922

This average type will give the total average all recorded responses.

For this study, we may want to get the total average as a comparison for the participant, maybe to provide a monetary incentive or encouraging message for smoking less in a later part of the study.

Average Type 2: Average last n days

Date 04/07/2024: Average([CigarettesSmoked], 3, 2, 5) = 3.409
Date 04/12/2024: Average([CigarettesSmoked], 3, 2, 5) = 2.556
Date 04/17/2024: Average([CigarettesSmoked], 3, 2, 5) = 2.643
Date 04/22/2024: Average([CigarettesSmoked], 3, 2, 5) = 2.333

This average type will average all recorded values a number of days from the day it’s calculating.

For this study, we may want to get an average every 5 days as a way to measure the participant’s progress.

Average Type 3: Average n days starting from a specific date

[QuitDate] = 04/08/2024

Average([CigarettesSmoked], 3, 3, 7, [QuitDate]) = 2.313

This average type can be used to specify an average a number of days after the specified date.

For this study, we may want to see if a week after the participant specified a Quit Date if their average cigarette usage is lower than their total average. which could be used to add a bonus monetary incentive.

Average Type 4: Average n days ending on a specific date

Average([CigarettesSmoked], 3, 4, 7, [QuitDate]) = 3.409

This Average Type will average all values a number of days prior to the specified date. Remember that this average will exclude all values recorded on the specified date from the calculation.

For this study, we may want to get a baseline average of how many cigarettes the participant smoked prior to their specified Quit Date. We can use this value as a general guideline for the participant’s progress on smoking less or compare their average towards the end of the stage/study to the beginning.

Average Type 5: Average last n responses

Date 04/08/2024: Average([CigarettesSmoked], 3, 5, 13) = 3.692
Date 04/15/2024: Average([CigarettesSmoked], 3, 5, 13) = 2.154
Date 04/20/2024: Average([CigarettesSmoked], 3, 5, 13) = 2.846

This average type is similar to Average Type 2, but instead of specifying the number of days, it specifies the number of responses for the total average.

For this study, we may want to check the average number of cigarettes the participant reports smoking. In this case, we may want this calculation to go off randomly using a Random Number Generator question to trigger this calculation in the assessment.

Average Type 6: Average n responses starting from a specific date

Average([CigarettesSmoked], 3, 6, 25, [QuitDate]) = 2.56

This average type is similar to Average Type 3, but instead of specifying the number of days, it specifies the number of responses for the total average.

For this study, we may want to get an average based on the responses in order to get an idea of how many cigarettes the participant will report when they answer this question.

Average Type 7: Average n responses ending on a specific date

Average([CigarettesSmoked], 3, 7, 15, [QuitDate]) = 2.933

This average type is similar to Average Type 4, but instead of specifying the number of days, it specifies the number of responses for the total average.

For this study, we may want to get an idea of how many cigarettes the participant typically reported prior to their quit date.

Average Type 8: Average since specific date

[MidDate] = 04/13/2024

Average([CigarettesSmoked], 3, 8, [MidDate]) = 2.55

This average type will take the total average starting on a date up to the most recent response entered.

For this study, we may want to find the average cigarettes smoked reported after the midpoint of the study and compare it to the average before the midpoint.

Average Type 9: Average until a specific date

Average([CigarettesSmoked], 3, 9, [MidDate]) = 3.161

This average type will take the total average starting on the first recorded response up to the specified date.

For this study, we may want to find the average cigarettes smoked reported before the midpoint of the study and compare it to the average after the midpoint.

Average Type 10: Average between a specified start and end date

Average([CigarettesSmoked], 3, 10, [QuitDate], [MidDate]) = 2.556

This average type will take the total average from the start date up to the quit date.

For this study, we may want to check if the participant is on a downward trend from their Quit Date up to the Mid Date. This can be used to predict if the participant will be successful in reducing their smoking habit.

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.

This formula can be used in any number of ways needed for a study which include, but is not limited to:

• Trigger calculations dependent on participant responses

• Save a specific value to display a question later on

• Save a specific value to trigger the end of a stage

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

DateDiff({Date/Time Minuend}, {Date/Time Subtrahend}, 'Unit difference')
Parameter 1. {Date/Time Minuend}
Parameter 2. {Date/Time Subtrahend}
Parameter 3. 'Unit difference'

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

2. Date/Time Subtrahend - The Date or Time that will be subtracted from the first parameter.

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

Parameter 1 and Parameter 2 can have a manually input date or time following this format:
Date - ‘yyyy-MM-dd’ (example: 2024-08-17)
Time - ‘HH:mm:ss’ (example: 17:15:32)

They can also have a Date or Time variable as the input as well as the following key words:
'now' → Date and time at the moment logic is evaluated
'today' → Date on which the formula is evaluated
'yesterday' → Yesterday’s date
'tomorrow' → Tomorrow’s date

For the ‘today’, ‘yesterday’, and ‘tomorrow’ key words, the Time component is defaulted to midnight, 00:00:00.

I.E. if today is 05/01/2024 12:39:42 PM, then:

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.

Contains({Checkbox Question},'Response Value to check')
Parameter 1. {Checkbox Question}
Parameter 2. 'Response Value to check'

1. Checkbox Question - Any checkbox question name

2. Response Value to check - a single number that matches the value of a response in the checkbox question. See example below.

Suppose a study wants to find a correlation of emotions to the amount of cigarettes a participant smokes. At the end of the day, the participant is presented with a checkbox question [FeelingToday] as follows:

Reflecting on today, which of these emotions did you feel? Check all that apply.

Then, in a calculated field question [Feeling], the following formula is calculated:

Iff(Contains([FeelingToday], 1), 1, 0) + Iff(Contains([FeelingToday], 2), 2, 0) +
Iff(Contains([FeelingToday], 3), 4, 0) + Iff(Contains([FeelingToday], 4), 8, 0) +
Iff(Contains([FeelingToday], 5), 16, 0) + Iff(Contains([FeelingToday], 6), 32, 0) = 17

The purpose of this formula is to determine what emotion, or combination of emotions, is associated with the amount of cigarettes the participant smokes. This could be used in an Average calculation when the participant enters the next stage to present helpful tips to the participant if they report feeling one of these emotions.

Alternatively, if the study wants to present messages to the participant if they responded feeling “Sad” which has a value of 2, then the following formula can be used in the Display Logic for the message:

Contains([FeelingToday], 2)

This would evaluate to TRUE and display the desired message to the participant.

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.

Variant 1: ResponseExists({Variable Name})
Variant 2: Exists({Variable Name})
Parameter 1: {Variable Name}

1. {Variable Name} - any question type that records a response from the participant

Suppose a baseline assessment contains two radio questions, [SmokerYN] and [VapeYN], asking the participant the following:

If the participant answers “Yes” to [SmokerYN], then they’ll be presented [VapeYN] to answer, otherwise, they don’t get shown [VapeYN].

Suppose this participant responds “No” in the assessment. After completing the baseline, they are moved into the next stage that has an assessment containing questions dependent on the participant’s answer to [VapeYN].

However, since the participant in this example didn’t see [VapeYN], these questions should include the following formula in their Display Logic:

ResponseExists([VapeYN])
OR
Exists([VapeYN])

Alternatively, we can add a calculated field question that has the following formula to determine if the participant answered the Vape question:

Iff(ResponseExists([VapeYN], 1, 0)

Putting variable names in square brackets […] will automatically return the last response value a participant has input regardless of whether the variable is in the same assessment or from an assessment two stages prior. This is especially important for the formulas described in this section to be used in Calculated Field Questions, Display Logic in assessments, and ending FormulaBased stages.

Suppose in a Baseline assessment, the participant is asked a Radio Question [DailyCigs] that says:

How many cigarettes do you typically smoke daily?

• 0 - 3 (value 1)

• 4 - 6 (value 2)

• 7 - 9 (value 3)

• 10+ (value 4)

Then in an assessment in a different stage, it uses the value from [DailyCigs] to present certain questions to the participant or a calculated field question can use the value to calculate/keep track of the difference between the users reported cigarette usage and how many they said in [DailyCigs].

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.

If no colon (:) is used, the following default values are used for the question types listed under them.

-999

• Dropdown

• Radio

• Slider

• RandomNumberGenerator

• Video

Empty String

• Text Field

• Text Area

1970-01-01 00:00:00

• Date

• Time

If a study is collecting PHI from participants, the information provided can be used to curate RichText and Email questions as desired. The table below shows what to use for each of the PHI fields offered in the EDC.

Suppose a study has an assessment that changes a participant’s mailing address. The following example shows what the RichText question will look like using the formulas and what will be displayed to the participant in the Insight app:

      Subject ID: PT015
      First Name: John
     Middle Name: PT015
       Last Name: Doe
  Street Address: 123 First St.
Apartment Number: 
            City: Townsville
           State: IL
        Zip Code: 12345

Now suppose the participant wants to change their address. They are shown a series of Text Field questions [NewStreetAddress], [NewApartmentNumber], [NewCity], [NewState], and [NewZipCode] as follows:

The participant can then be presented with their responses to verify if they are correct:

This information can be used in an email to be sent to researchers as follows:

{{Concat([CheckboxQuestion], “Concatenation Type”, ‘Delimiter’)}}

Concat, short for “concatenation,” is a special formula that can list out the selected responses from a Checkbox question type. There are 3 concatenation types as explained below:

[Question] = Checkbox Question type

This can be used to display a list of sample goals a participant can achieve in a RichText question and be an easier way to read a participant’s response in an Email.

{{DateFormat([Date/TimeQuestion], ‘Desired formatting’)}}

DateFormat is a special formula that formats a Date or Time question type. The table below explains and shows the different formatting that is possible:

[Date] = ‘2024-03-08’
[Time1] = ‘17:03:06’
[Time2] = ‘08:17:53’

Format

Explanation

Formula