How to calculate percentage change applicants by school year over year?

[#1]

I'm working on a project examining the number of applicants to highly selective universities in the United States from 2011-2022.  I'd like to calculate the year-over-year percentage change of the total number of applicants by school.  I have tried to work with "group by" and also create a lag variable, but I can't quite figure it out.

Help -- any ideas on how to do this?  Thanks! 

[#2]

I'd suggest restructuring the data set using Tabulate.  Put institution in the row area and year (after changing it to nominal) in the column zone and then put the total applicants in the body of the table.  Then make that into a data table and you should be able to compute the annual changes.  I've attached the restructured table.

[#3]

Thanks.  I appreciate the creativity here, but it seems like I'd have to then do several new columns and formulas to get the year over, and it would be hard to turn into a graph.  Also, I have several other measures I'd like to compute YoY data for, which would also be difficult to keep all of the data straight and together.  

I'm guessing there isn't a better way to do this?

[#4 · Accepted solution]

See if this is close to what you want:

Names Default To Here( 1 );
dt = Current Data Table();

dtPercent = dt << subset(
	columns( :Institution name, :Year, :Applicants total ),
	selected rows( 0 )
);
dtPercent << sort( by( :Institution Name, Year ), replace table( 1 ) );

dtPercent << New Column( "Percent Change",
	Format( "percent", 9, 2 ),
	set each value(
		If( Row() == 1 | Lag( :Institution name ) != :Institution name,
			:Percent Change = 0,
			:Percent Change = 1 - Lag( :Applicants total ) / :Applicants total
		)
	)
)
;

[#5]

Great solution -- thank you!

[#6 · Accepted solution]

You can also use Col Moving Average() to calculate value from earlier row for a group and then calculate with that (data must be sorted by year)

1 - (Col Moving Average(:Applicants total, 1, 1, 0, 1, :Institution name) * 2 -
:Applicants total) / :Applicants total

Here data is also sorted by Institution but it isn't necessary

[#7]

Another great solution.  Thanks!