This page contains the following commonly used formulas:
Formula to display Null values as 'Unanswered' or 'Unknown'
Formula to display Age Ranges
Add days to a date object or variable
Getting a count of non-exclusive values
Creating a Running Count within a section that restarts in each section
Return only the date from time/date field
To capture a user prompt for the Active, Enrolled or Dismissed filters
Convert minutes to numbers
To set the Age of a participant to match the Ending Date range of the prompt
To identify an Age in months for children under one year of age
Returns a participant’s age based on today’s date
Return the date of participant’s 18th birthday
Flag a participant who has more than one record of a tier 2 form
Flag and hide empty columns
Formula to display Null values as 'Unanswered' or 'Unknown'
=If IsNull([Data Object]) Then "Unanswered" Else [Data Object]
or
=If [Data Object]>="1" Then [Data Object] Else "Unanswered"
Example to show a value of 'unanswered' for participants with an empty value for Race while showing the responses values for those who have data in this field the formula will read as follows:
=If IsNull([Race]) Then "Unanswered" Else [Race]
or
=If [Race]>="1" Then [Race] Else "Unanswered"
Formula to display Age Ranges
=If [Age] <10 Then "1-9" ElseIf [Age]<20 Then "10-19" ElseIf [Age]<30 Then "20-29" ElseIf [Age]<40 Then "30-39" ElseIf [Age]<50 Then "40-49" ElseIf [Age]<60 Then "50-59" ElseIf IsNull([Age]) Then "No Age Listed" Else "Over 60"
To change the age ranges, adjust the numbers next to the less than operators and within the quotes accordingly.
For example: To change the third age range in the above formula from a range of "20- 29" to a "20-25" age range; that section should read as follows: <26 Then "20-25" ElseIf [Age]<36 Then "26-35" etc.
Add days to a date object or variable:
=FormatDate(RelativeDate([Begin Date];7);"MM/dd/yyyy")
[Begin Date] can be any date object or variable. This adds 7 days to the [Begin Date].
Getting a count of values for multi-select fields:
=Sum(If(Match([Object Value]; "*Lunch*")) Then 1 Else 0)
Creating a Running Count within a section that Restarts in Each Section
=RunningCount([object to count];([object being sectioned]))
Return only the date from time/date field:
=ToDate(Left(UserResponse("Begin Date");Pos(UserResponse("Begin Date");" ")-1);"MM/dd/yyyy")
Convert minutes to numbers:
=FormatNumber(Floor([Number]/60);"00")+":"+FormatNumber(Mod([Number];60);"00")
[Number] is any number object that you set up either as a variable or from universe objects.
Common Age/Birthdate Formulas:
Basic Formula for Currect Age:
=DatesBetween([Date of Birth];CurrentDate();YearPeriod)
You'll do a DatesBetween calculation to find the time between your date element (in this case Date of Birth) and the Current Date. The final piece (YearPeriod) signifies the data type you'd like.
For more on the DatesBetween formula, click here.
To set the Age of a participant to match the Ending Date range of the prompt:
=Floor(DaysBetween([DOB];[End Date])/365.25)
[End Date] can be exchanged with any date in your report.
To identify an Age in months for children under one year of age:
=FormatNumber(Sum(DaysBetween([DOB detail];[End Date])*12)/365.25;"#,##0.0")
Returns a participant’s age based on today’s date:
=Floor(DaysBetween([DOB];CurrentDate())/365.25)
"CurrentDate" function can be replaced with another date if you need to know a participant’s age as of a specific date.
Return the date of participant’s 18th birthday:
=MonthNumberOfYear([DOB]) + "/" + DayNumberOfMonth([DOB]) + "/" + FormatNumber(Year([DOB])+18;"00")
Flag a Participant who has more than one record of a tier 2 form:
=(If count([Record ID])>1 then 1)
where "xx" in [Record ID] is the unique ID of the record. This flag will return a "1" in a column if a participant has more than one record
Flag and hide empty columns
= If (Length([Data Object])) > 0 Then 1 Else 0
This flag will return a "1" in a column if the chosen Data Object contains data. Filter on the column for value = 1 to only show those that contain data.