Help with Multi-Row Formula Tool

[melaniekim1121]

Hi all, I have a tricky one for you. 

I'm using call center data to develop a report that has repeat callers. The criteria for a repeat caller would be anyone who calls 5+ times within a 7 day period. An example would be 4/2/2024 - 4/8/2024. I have been able to calculate the 7-Day Interaction Count, as well as Max 7 Day Interaction Count (which may not be needed here) for a group of callers. 

 

Now I am trying to remove the calls for these members that do not fall within the 7-day Interaction Period. I am inputting dummy data with the "Tag" column that has either a "Y" or "N" to indicate the data point I am trying to tag each row with. I also included a column "Reasoning behind Tag" that gives the reasoning behind why it is tagged as either a "Y" or a "N". 

I created a "5+ Flag" column to mark the ones with 5 or more interactions with a "Y" and I feel like I may need to use a multi-row formula tool to fill in the rest, but I have no idea how to tell it to fill in 5 rows with "Y" starting at the 5 for "7 day Interaction Count."

I hope I was articulate enough for someone to be able to assist me. I welcome any help or questions you might have! Thank you in advance! 

[LindonB]

Hi there. Seems you've got a lot of it solved. Is the goal just to determine if the 5+ threshold was violated on the prior row in the new field?

If so, you can use the following expression for the multi-row tool.
5+ Flag:
IF [Row-1:ID]!=[ID] THEN [Tag]
ELSEIF [Tag]='Y' OR [Row-1:5+ Flag]='Y' THEN 'Y'
ELSE [Tag] ENDIF

Still that will still have 'N' until a 5+ row is reached. If you are just trying to find whether the person is a repeat caller (not if the interaction is a repeat call or call driver), I would just use a summarize tool to group on the person (ID) and return the max of the max field. Then filter for those with 5+.

[leefarrell]

Sort Your Data: First, sort your data by caller and date.

Add Multi-Row Formula Tool: Use this tool to create the "5+ Flag" column.

Set Up the Formula:

Use a conditional formula to check if the current row and the next four rows (within the same caller) have 5 or more interactions within the 7-day period.
If the condition is met, tag the rows with "Y"; otherwise, tag with "N".
Here’s an example of the formula:

alteryx
Copy code
IF
[Row-1:CallerID] = [CallerID] AND
[Row+1:CallerID] = [CallerID] AND
[Row+2:CallerID] = [CallerID] AND
[Row+3:CallerID] = [CallerID] AND
[Row+4:CallerID] = [CallerID] AND
DateTimeDiff([Row+4:CallDate], [CallDate], "days") <= 7
THEN "Y"
ELSE "N"
ENDIF
This formula checks for 5 calls within the same 7-day period for the same caller. Adjust as needed for your specific dataset.

[nishamarrshallll]

Hello

I'm putting up a report that will list repeat callers that make five or more calls in a seven-day span. After calculating the 7-Day Interaction Count, I must now eliminate calls that fall outside of this time frame. I'm assigning the values "Y" or "N" to data points and providing an explanation. For those with five or more interactions, I made a "5+ Flag" column, but I'm not sure how to utilize a multi-row formula tool to fill in five rows with "Y" beginning with the fifth interaction. I would welcome any questions or advice.

Regards

Nisha Marshall

Prestige Evergreen

[melaniekim1121]

Hi there, apologies for the confusion. I only have the "5+ Flag" column. I do not have the "Tag" or "Reasoning behind Tag" columns in my data. I simply used dummy data and added these two columns to show my reasoning behind what I need. I am trying to make the "5+ column" look like the "Tag" column. 

[melaniekim1121]

Hi leefarrell,

Thank you for your response. The Alteryx multi-row formula tool is not recognizing the Row+2 and beyond variables (error says: Unknown variable "Row+2:ID"). It looks like it can only reference one row before or after the active row. Am I perhaps doing this incorrectly? I am including the screenshot of the error. 

 

[AndrewDMerrill]

You need to increase the "Num Rows" numerical input in order to capture more distant rows. In the image you shared, the box is just beneath the "Create New Field" text box near the top of the configuration pane for the tool.

Set it to 4 so that you can access [Row+4:ID]

[melaniekim1121]

Thanks AndrewDMerrill,

I am now able to reference the +4 rows. leefarrell's formula still doesn't work with my workflow. All rows were marked as "N." 

I tried updated the formula, but I'm unsure how to change 4 rows below the rows that have a "7-Day Interaction Count" of 5. I need to update all five rows. My new formula is only updating the current row but I need to update the below 4 rows to "Y" as well. 

[melaniekim1121]

I think I got it. This is the new formula I made: 

IF [7 Day Interaction Count] >= 5 AND
[Row+1:ID] = [ID] THEN "Y"

ELSEIF [Row-1:7 Day Interaction Count] = 5 AND
[Row+1:ID] = [ID] THEN "Y"

ELSEIF [Row-2:7 Day Interaction Count] = 5 AND
[Row-2:ID] = [ID] THEN "Y"

ELSEIF [Row-3:7 Day Interaction Count] = 5 AND
[Row-3:ID] = [ID] THEN "Y"

ELSEIF [Row-4:7 Day Interaction Count] = 5 AND
[Row-4:ID] = [ID] THEN "Y"

ELSE "N"
ENDIF