Change to find percent change from 2012 to 2022

Question

Good afternoon -- I'm working on a dataset that has the number of applicants to different columns.  I previously asked how to get the yearly percent change for a number of applicants.

I would now like to just get the percent change from 2012 to 2022.  I have tried a lag column and also modifying the numbers in the formula in the current column that calculates percent change, but I can't seem to figure it out.  I appreciate the help in advance.  Also, I've been trying to find what the numbers 1,1,0,1 reference as I think that has to do wth the solution, but can't find it.  Thanks!

Solution with col moving average.jmp

jthi · Accepted Answer

Are you trying to find the change between first year and last year or specifically 2012 and 2022?

jthi · Accepted Answer

Depending on the assumptions which can be made (is ordered by year / do you want first vs last year) there are different solutions. Below is one option for 2012 vs 2022

Col Sum(If(:Year == 2012, :Applicants total, ., ), :Institution name)
	/ 
	Col Sum(If(:Year == 2022, :Applicants total, ., ), :Institution name)

txnelson · Accepted Answer

Expanding on Jarmo's solution, you may only want the 2012 to 2022 change to be displayed in the 2022 rows

The formula for this is

If( :Year == 2022,
	Col Sum( If( :Year == 2012, :Applicants total, . ), :Institution name ) /
	Col Sum( If( :Year == 2022, :Applicants total, . ), :Institution name ),
	.
)