Related: Finding the Most Recent TouchPoint Response

Using Flattened Data

To find the 'Nth' most recent touchpoint response, you will first need to create a variable to rank the responses.

Response Rank: this will rank responses in reverse chronological order
=Rank
([Date Taken_272];[Response ID_272];[Participant Site Identifier]) In ([Response ID_272])

After you create the Response Rank variable, you can use it to find the most recent responses.

Most Recent Response: this will populate only the answer from the most recent response
=(If [Response Rank] = 1 Then ([Date Taken_272)) In ([Response ID_272];[Participant Site Identifier])
*You can change the Date Taken object to whatever you want the field to populate with. (Ex: "Most Recent Response"; [TP Answer]; 1; etc.)

2nd Most Recent Response: this will populate only the answer from the 2nd most recent response
=(If [Response Rank]=2 Then ([TP ANSWER])) In ([Response ID_272];[Participant Site Identifier])

You can continue this pattern for as many responses as you need by increasing the [Response Rank] = value.


Putting these variables into a table with the Participant Site Identifier may inflate your data and return a null value along with your variable value, so you'll see 2 lines per participant. To fix this, you need to create a Participant scoped version for each variable. This is the one you will use in a table:

Most Recent Response - Participant: this will assign the most recent answer (with no null) to the participant

=Max([Most Recent Response]) In ([Participant Site Identifier])

2nd Most Recent Response - Participant: this will assign the most recent answer (with no null) to the participant

=Max([2nd Most Recent Response]) In ([Participant Site Identifier])

You can then use the variables in a table.

Did this answer your question?