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_XX];[Response ID_XX];[Participant Site Identifier]) In ([Response ID_XX])
Replace any XX with the appropriate ID for the TouchPoint.
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_XX)) In ([Response ID_XX];[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_XX];[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.
