I have a diagonal metric that loops over multiple cohorts which i'm trying to convert into a vertical table, the reason for this is for future computations extracting the entire column would be simpler then applying offsets. The age is set at the age of entry, with a minimum age of 18. for a 18yr old joiner, in year 1 then are "1", in year 2 they would be 19, year 3 then would be 20.... for the example data i've assigned the value based on the age at commencement to hopefully show this better. my actual data has 63 cohorts, ages 18-60 with year 1 to year 20, which may change over time. Original table: Cohort age Year 1 year 2 year 3 year 4 1 18 1 1 19 2 1 1 20 3 2 1 1 21 4 3 2 1 2 18 1.5 2 19 2.5 1.5 2 20 3.5 2.5 1.5 2 21 4.5 3.5 2.5 1.5 Required output, I will need Cohort and Age at commencement as hierarchical headers but couldn't get the markdown to format correctly: Cohort Age at Commencement 1 18 1 19 1 20 1 21 2 18 2 19 2 20 2 21 Year 1 1 2 3 4 1.5 2.5 3.5 4.5 year 2 1 2 3 1.5 2.5 3.5 year 3 1 2 1.5 2.5 year 4 1 1.5 I've tried pivot, melt, etc but am struggling to then format into the desired output. Continue reading...
