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)

...

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 evaluates 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 Date (and Time, if available). Parameters include:

1. The Date (and Time, if available) from which the other date will be subtracted from.

2. The Date (and Time, if available) which 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 yesterdays date and the date and time right now.
  If the yesterdays’ date is 07/10/2023 and we are evaluating this function at 07/11/2023 at 4:07pm then the result should be about: -2407.5039294216667

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)

...

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])

The constructs last() and [*] existed in CMS but they don’t exist in V2.

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.

...