topic Re: Change to find percent change from 2012 to 2022 in Discussions

Change to find percent change from 2012 to 2022

AdditiveOwl2917 — Sat, 27 Jan 2024 19:55:26 GMT

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!

Re: Change to find percent change from 2012 to 2022

jthi — Sat, 27 Jan 2024 20:16:08 GMT

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

Re: Change to find percent change from 2012 to 2022

jthi — Sat, 27 Jan 2024 20:34:09 GMT

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)

Re: Change to find percent change from 2012 to 2022

txnelson — Sun, 28 Jan 2024 04:19:37 GMT

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 ),
	.
)

Re: Change to find percent change from 2012 to 2022

AdditiveOwl2917 — Sun, 28 Jan 2024 13:20:24 GMT

Thank you all -- that worked great. I'm just getting into JSL, so your examples are incredibly helpful. Thanks!