Cohort Change -- Diagonal Calculation

[abrasch]

I'm hoping to achieve the same result in Alteryx as a simple set of calculations in Excel. The data is structured like the screenshot below (Excel file attached). The goal is to calculate the ratio of change over time for a given cohort (e.g., K cohort in 2017 moves into 1st grade in 218, gaining 6 students, or a ratio of 1.061). Any help would be much appreciated.

[flying008]

Hi, @abrasch 

There is a rapid method for you reference, but i can't upload any .yx** file to web, So perhaps someone else can solve this problem in a conventional way.

Input
Grade	2017	2018	2019	2020	2021	2022
0	98	102	85	86	77	82
1	119	104	93	85	96	89
2	101	120	93	91	95	92
3	105	114	102	93	89	110
4	131	116	116	101	79	82
5	125	131	114	114	95	87
Output
Grade		Ratio 2017 to 2018	Ratio 2018 to 2019	Ratio 2019 to 2020	Ratio 2020 to 2021	Ratio 2021 to 2022
0
1		1.06122449	0.911764706	1	1.11627907	1.155844156
2		1.008403361	0.894230769	0.978494624	1.117647059	0.958333333
3		1.128712871	0.85	1	0.978021978	1.157894737
4		1.104761905	1.01754386	0.990196078	0.849462366	0.921348315
5		1	0.982758621	0.982758621	0.940594059	1.101265823

[Qiu · Accepted solution]

@flying008 
The Diagonal tool looks interesting.

@abrasch 
I have to choose a batch macro for this one.

I pick up one year and the year after it for each batch.
And I iike this question, maybe we can submit it for the weekly challenge?

[abrasch]

@Qiu 
Thank you for the quick response. I was having an issue with the year-to-year nature of the data restructuring that had to occur--great solution with the batch macro! I tested and it worked as expected and will be scalable in terms of adding more grades and years. Thanks again for you help. I will submit it as an idea for the weekly challenge.

@flying008 
That looks nifty! It would be interesting to see the inner workings of the macro.

[Qiu]

@abrasch 
Really happy to know it helps and thank you for great feedback.

Have a good one.😀