TouchPoint Responses are linked to Programs but not to specific Program Enrollments, so if you allow Participants to be enrolled in a Program multiple times and you need to know which Responses were taken within each enrollment, follow these steps.
Using Flattened Data
When you are working with Flattened TouchPoint data, pulling in the Program name from the Enrollment folder could inflate your data, creating duplicated Response IDs.
Step 1: Build Your Query
Include the following Objects in the Query from the Program Enrollment folder: [Program Name] and [Program Unique Identifier]
Run your Query.
Notice that for the same Participant, the same TouchPoint Response duplicates for each Program the Participant is enrolled in.
This is because TouchPoint Responses are linked to the Participant and Program Enrollment is linked to the Participant, but TouchPoint Responses and Program Enrollment are not linked to each other. Because of this, the report overcompensates by creating a line of data for each possible combination, which inflates the data, or creates a Cartesian product.
If you remove Program Enrollment data from the table, the duplicated rows are removed, as see below.
Step 2: Flag a TouchPoint Taken in a Date Range
Make sure you have [Program Start Date] and [Program End Date] from the Program Enrollment folder in your Query. Add these objects to your table.
You may have Participants that do not have a Program End Date. We'll come back to this later.
Create a Variable called Flag - TP Completed in Enrollment Date Range using the Formula:
=(If [Date Taken_XXX] Between([Program Start Date];[Program End Date]) Then 1)
Note: Replace all XXX with the appropriate IDs
Add the Flag to the table. This will assign a 1 to any Response taken during the Enrollment date range.
You'll notice that any Participant who does not have a Program End Date does not get flagged. We'll need to add a Variable to account for a null Program End Date.
Step 3: Create a Variable for Null Program End Date
Create a new Variable called Program End Date New using the following Formula:
=(If IsNull([Program End Date]) Then CurrentDate() Else [Program End Date])
You can pull it into the table next to Program End Date to see the difference. This Formula replaces the null value with today's date.
Step 4: Add Program End Date New to your Flag Variable
Update Flag - TP Completed in Enrollment Date Range to include the Program End Date New Variable.
Flag - TP Completed in Enrollment Date Range
=(If [Date Taken_XXX] Between([Program Start Date];[Program End Date New]) Then 1)
You should now see a Flag on anyone currently enrolled.
Step 5: Add Program Clause to Flag Variable
Now you'll want to ensure that the Flag is only flagging the Program that the Response was recorded in. With the current Flag, it is flagging anyone whose Program start/end dates lie in the date range in any Program.
Add an additional clause to the Flag - TP Completed in Enrollment Date Range Variable.
=If [Date Taken_1566] Between([Program Start Date];[Program End Date New]) And [Completing Program Unique Identifier_XXX] = [Program Unique Identifier] Then 1
Your Flag Variable should now look like this:
With the Flag in the table, only the Response within the Program Enrollment date range and the Program where the TouchPoint was completed is being flagged.
Step 6: Filter the Table using the Flag Variable
To add a Filter to the table, Highlight table > Report Element Data > Display Filter Panel > Drag flag variable into filter
Set the Flag - TP Completed in Enrollment Date Range equal to 1.
With the Filter applied to the table, you should only see 1 row of data for each TouchPoint Response ID. You can hide the Flag Variable from the table or remove it altogether. The Filter will stay attached to the entire report.