Calculating Differences Between Records in Increments of 1, 5 and 10

[HTDuck]

Hi All - 

I'm struggling with creating calculations on the base shown below. What I'm trying to do is calculate the % change in total enrollment growth by 1YR, 5YR and 10YR between Fall Years for each university (it would be a changing calculation with each new Fall Year record added for each university).

While this is easy to do with a vlookup in Excel (i.e. matching University, Fall Year and Total Enrollment with Fall Year-1, Fall Year-5, Fall Year-10), I'm struggling with a finding solution here in Airtable.

I'm feeling like it might require a linked Lookup table (or adding another field) to make it work. But just not sure how to go about it. Or, if it's even possible between records in Airtable. 

The numbers shown below for the % growth fields were imported from Excel. 

Any help would be greatly appreciated!

Thanks!

[Dimitris_Goudis]

Hey @HTDuck, 

Thanks for posting on community.

To solve this issue you have to change the data structure in your table like following: 
table 1 -> add the annual values per university -> each record will be the annual value for one university
table 2 -> Reporting table -> in this table each record is the university for the which you need to run the calculations. You need to add new records here only when you want to append your final list of universities 

Then you need to link those tables with each other, and every time you add the annual value in table 1 you will add link this value with the right university in table 2, so you will update the linking fields from table 1 (that will be easier for you) 

* Note: if you receive those values from a form you can add the linked to field in your form or you ca use an automation

Last step is to create the lookup fields in table 2 and you are done. 

For any kind question please don't hesitate to text back. 

Yours sincerely, 
Dimitris  Goudis 

[HTDuck]

Hi Dimitris - Thanks for the response! Conceptually, I get what you are saying, but having some difficulty visualizing/figuring out how to actually do that. It's been a while since I've setup linked tables and lookup fields. 

Additionally, each Fall Year, I will add another record in the primary table for each university, i.e. 2023, which would then become the most recent Total Enrollment that would need to be calculated from, i.e. 1YR Growth from the previous total enrollment, and 5YR Growth and 10YR Growth from the most recent total enrollment. The -1, -5 and -10 Fall Year's would always be based upon the most recent record entry for each university, which will be variable (i.e. some schools will have 2022 as the most recent, others 2021 or 2020, for example). While that formula can be created in Excel or a database calculation, I may be asking too much of Airtable here. My goal was simple to be able to have that 1YR, 5YR and 10YR growth calculated dynamically, vs. having to manually calculate and enter it. 

Thanks for your help Dimitris!

[Dimitris_Goudis]

Hey @HTDuck, 

In your primary table you will have the annual results. In this table the 1-year, 5 year, 10 years results cant be calculated, so you can delete the those columns. 

Then you will create a new table to store universities and link this table with your primary. Then on primary table match the right university with the right result. Then go to table were all universities are listed and add lookup and rollup fields to create the calculations you need for 1,5,10 year. 

For any kind of question or any struggle dont hesitate to dm me. 

Yours sincerely, 
Dimitris Goudis 

[HTDuck]

Hi Dimitris - 

Thanks for your help! I've tried to setup this up and I'm just not fully grasping what I need to do with Table 2. 

I've setup a column with the University Name, but am at a loss of what to select when creating linked records and which specific columns in Table 1 to link to. When I select "Link to to Record" when adding a field to Table 2, it asks me which lookup fields to include. I'm assuming it would include "University, Fall Year and Total Enrollment," which just adds three blank fields in Table 2. I'm not quite sure how to add lookup and rollup fields. Everything I've tried just ends up with blank cells (Table 2 - Universities):

Table 1 (Enrollment) for reference:

I just can't figure this out, even before I get to trying to setup calculations for 1, 5 and 10 year growth-which is a whole other issue calculating, as the Fall Year records will change from year to year (i.e. most recent records - 1 year, 5 year and 10 year growth). Thanks! Howard 

Any help would be greatly appreciated.  

[Dimitris_Goudis]

Hey @HTDuck 

Please check my guide here. I created a short video showing the right setup and field creation. Due to you want the totals from multiple years you have to use rollup instead of lookup. 

For any kind of question please do not hesitate to dm me.

Yours sincerely,
Dimitris Goudis 

[HTDuck]

Dimitris -

Thank you so much for this!!! I greatly appreciate your time helping me with this. I'm getting that you just have to think about things differently when working with Airtable. It seems you have to create another table that pulls from your original table in order to do any calculations or run automations. I really didn't think it was going to be this complex.

I'm struggling with what to do next.

I'm assuming calculating a 1YR or 5YR growth % change would have to be done with some calculation of {Fall Year} - 1 or {Fall Year} - 5, etc. i.e. (Total Enrollment / Total Enrollment=Fall Year-5)/Total Enrollment) via an automation or formula that looks for University Name, Total Enrollment and Fall Year. I just can't find anything in the discussions or Airtable examples that demonstrates something similar.

This was easy to do with an if/then vlookup formula in Excel, but I have spent way too many hours trying to figure it out with Airtable. I have greatly appreciated your help, but feeling a little greedy at this point!

Thanks!

[Dimitris_Goudis]

Hey @HTDuck, 

Please don't be disappointed it is the learning curve stage for this new app for you. Airtable while has many common things with excel, what makes it more powerful is that applies the rules of data bases. 

More specifically on data bases every asset you have needs its own table and if those assets are related with each other you have just to add the relationship between them. In your example you have 2 assets, the universities and the annual data. These are different information (assets) but the are linked with each other, right? 

In order to achieve your final goal you just need to add the some rollup fields in universities' table. There is no need to run the calculations with Automations... 

I have already DM you about airtable improvements for your project. 

Yours sincerely,
Dimitris Goudis