Hi Alteryx community!
I have been wrecking my brain over this, and have tried Googling/YouTubing but having a really hard time articulating this problem.
Very comfortable with this analysis comparing 2-10 reports/tables/inputs, but not so much with 200+ reports stacked together:
| Input |
| Customer ID | Report Date |
| 111 | 9/1/2020 |
| 222 | 9/1/2020 |
| 333 | 9/1/2020 |
| 444 | 9/1/2020 |
| | |
| 222 | 10/1/2020 |
| 333 | 10/1/2020 |
| 444 | 10/1/2020 |
| 555 | 10/1/2020 |
| 666 | 10/1/2020 |
| | |
| 111 | 11/1/2020 |
| 222 | 11/1/2020 |
| 333 | 11/1/2020 |
| Output |
| | Report Date | Common | Dropped | Gained |
| Row 0 | 9/1/2020 | - | - | - |
| Row 1 | 10/1/2020 | 3 | 1 | 2 |
| Row 2 | 11/1/2020 | 2 | 3 | 1 |
Commentary:
Row 1: We have 3 in common with prior row (222/333/444), 1 dropped (111), 2 gained (555/666)
Row 2: We have 2 in common with prior row (222/333), 3 dropped (444/555/666), 1 gained (111)
Prefer not to overcomplicate with an iterative/macro approach, but any ideas welcome. Thank you!
