Cohort Change -- Diagonal Calculation

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. Cohort Change.png

Cohort Change.xlsx

Transformation

Accepted answers

Qiu

@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?

1103-abrasch.yxzp

All comments

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.

录制_2023_11_03_15_16_35_867.gif

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

@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?

1103-abrasch.yxzp

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.😀

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703