Left, Right, and Full Outer Joins (Customer Gain, Loss analysis) with Formula

[Ind4DT]

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!

[Greg_Murray · Accepted solution]

Hi @Ind4DT,

My output differs from yours a bit but I think it is on the right track

hope this helps,

Greg

[Ind4DT]

Looks great, thank you!