The posted solution as of 9/20/19 seems to be modified from the original dataset and references fields that are now missing, which throws off the dynamic rename module so the final output column names are unclear.
Attaching my solution from the new dataset:
Careful not to click the text input tool for the solution, it will freeze Alteryx for a while!
I'll admit to getting a hint on how to simplify this one, but then was able to complete on my own. Very clever way to simplify.
Very useful exercise to see how to use tools to simplify processes
Spoiler
The transpose and cross-tab made this much easier than having a dozen multi-row tools.
Process
- Add Record IDs to allow for transpose and cross-tab
- Transpose on Record ID, HP category, Year, and Month
- Use two Multi-Row tools (one for 3mo average, the other for 6mo average)
-- Had "Values for Rows that Don't Exist" set to values of closest valid row to enable it to deal with initial months and nulls
- 3 cross-tabs to move back to original orientation
-- Original data
-- 3mo
-- 6mo
- Dynamic Renames to add "r3mo_" or "r6mo_" to start of field name
- Join Multiple to bring it all together
Process
- Add Record IDs to allow for transpose and cross-tab
- Transpose on Record ID, HP category, Year, and Month
- Use two Multi-Row tools (one for 3mo average, the other for 6mo average)
-- Had "Values for Rows that Don't Exist" set to values of closest valid row to enable it to deal with initial months and nulls
- 3 cross-tabs to move back to original orientation
-- Original data
-- 3mo
-- 6mo
- Dynamic Renames to add "r3mo_" or "r6mo_" to start of field name
- Join Multiple to bring it all together
