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 taken the same TouchPoint more than once
Formula to display Null values as 'Unanswered' or 'Unknown'
=If IsNull([Data Object]) Then "Unanswered" Else [Data Object]
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]
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 non-exclusive values:
=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")
To capture a user prompt for the Active, Enrolled or Dismissed filters:
=ToDate(Left(UserResponse("Begin Date");Pos(UserResponse("Begin Date");" ")-1);"MM/dd/yyyy")
The text between the quotes can be changed to any prompt that you create with any date object. This formula removed the time stamp from the auto filter, which is necessary when using the date object in formulas.
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:
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 taken the same TouchPoint more than once:
=(If count([Response ID_xx])>1 then 1) in ([Participant Site Identifier])
where "xx" in [Response ID] is the unique ID of the TouchPoint. This flag will return a "1" in a column if a Participant has taken a TouchPoint more than once.