A common need in ETO Results is to identify the most recent response by a Participant to a particular TouchPoint question. There are a variety of methods to construct Variables that will determine this. In many cases, this can be obtained with a single Formula. The method will vary depending on whether your Query is using Flattened data (each question is contained in its own Object, usually used in custom Universes) or Unflattened data (all questions are contained in the [Question]/[Answer] Object pair, used with the Standard TouchPoint Universe NEW).
If Your Report Is Using Flattened Data
To obtain a Participant's most recent response to the question "What is your question?" on the TouchPoint "This Is My TouchPoint," use the following Formula construction:
=Last([What is your question?_1234] In([Participant Site Identifier];[Date Taken_99];[Response ID_99])) In([Participant Site Identifier])
Your Query must include the Date Taken and Response ID for the TouchPoint. The numbers following the underscore will vary for each customer.
You can use either [Participant Site Identifier] or [Subject Identifier], depending on the needs of your report.
How it works:
The input context (the first In() statement, inside the Last() parentheses) to Last() tells it the order to sort in, to determine what "last" means in this case. The order of Objects in the In() input context matters! In this case, this is like creating a table with Participant ID, Date Taken, Response ID, and the question, in that order. In this imaginary table, the last entry for each Participant will be the most recent, because the data will be sorted for each Participant first by date; then, if there is more than one response on that date, by Response ID, with the higher response ID being the later response.
The value actually returned by Last() is controlled by the output context (the second In() statement, outside the Last() parentheses). Here, this means that Last() looks at each Participant within this imaginary table and finds that Participant's last entry, which according to the sort order we specified will be the most recent, and returns that value. The result will be each Participant's most recent answer to the question.
If Your Report Is Using Unflattened Data
Last() Function
To obtain a Participant's most recent response to the question "What is your question?" on the TouchPoint "This Is My TouchPoint," use the following Formula construction:
=Last([Answer] Where ([Question] ="What is your question?" and [TouchPoint Name]="This is My TouchPoint") In ([Subject Identifier];[Date Taken];[Response Unique ID_Form ID])) In ([Subject Identifier])
Your Query must include the Question, TouchPoint Name, Date Taken and Response Unique ID_Form ID for the TouchPoint.
You can use either [Participant Site Identifier] or [Subject Identifier], depending on the needs of your report.
Most Recent - 3 Variable Solution
If the [Most Recent] Object does not produce the expected result, use the following 3-Variable construction:
Variable 1: Most Recent Date
=Max([Date Taken] Where([Question]="What is your question?" And [TouchPoint Name]="This Is My TouchPoint")) In([Subject Identifier])
Variable 2: Most Recent Response ID
=Max([Response Unique Identifier]) Where([Question]="What is your question?" And [TouchPoint Name]="This Is My TouchPoint" And [Date Taken]=[Most Recent Date]) In([Subject Identifier])
Variable 3: Most Recent Answer
=Max([Answer]) Where([Question]="What is your question?" And [TouchPoint Name]="This Is My TouchPoint" And [Response Unique Identifier]=[var2]) In([Subject Identifier])
How it works:
Variable 1 identifies the most recent date your TouchPoint was taken.
Variable 2 uses the first Variable to identify the Response ID that corresponds to the most recent date. This step is necessary because multiple different TouchPoints may have been taken on that same date.
Variable 3 then obtains the answer from the second Variable's response ID and returns that answer.
Most Recent With Table Context
This Variable may work as a simpler option for some reports.
This Object contains only the output context In(Participant ID)
This approach may be helpful in Unflattened data where you have the same question across multiple TouchPoints
=Last([Answer] Where([Question]="This is my question?")) In([Participant Site ID])