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

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:

  • =[Answer] Where([Question]="What is your question?" And [TouchPoint Name]="This Is My TouchPoint" And [Most Recent]="Yes") In([Subject Identifier])
  • Your query must include the Most Recent object, which can be found in the "Details" subfolder of the "TouchPoints" folder in the query universe.
  • You can use either [Participant Site Identifier] or [Subject Identifier], depending on the needs of your report.

How it works:

  • Each part of the Where() statement further limits the value of [Answer] to the desired result.  [Question]="What is your question?" limits Results only to the desired question. [TouchPoint Name]="This Is My TouchPoint" limits only to the desired TouchPoint. If your query references only one TouchPoint, or if the question is guaranteed to be unique across all TouchPoints in your enterprise, this part is optional.
  • [Most Recent] is a universe object that will be Yes if a given response to a given TouchPoint, for a given participant, is the most recent, according to Date Taken and Response ID. Otherwise, it will be No for that response. [Most Recent] is scoped to participant AND TouchPoint, so each individual participant will have a Yes for their most recent response on every TouchPoint they have taken.
  • Finally, the output context applies the result to the participant.  It is not necessary to include a Max() or other qualifier, since Most Recent will ensure only a single answer will be returned and prevent a #MULTIVALUE error.

If the [Most Recent] object does not produce the expected result, use the following three-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.

If you have additional questions or need more help, please feel free to contact Customer Service.

Did this answer your question?