Skip to main content

I’m trying to bring in a spreadsheet into a Base.


Each month, we pull a report that tells us how many people, per department, we have.

Department 1 = January '19 has 32, December '18 has 28, November '18 has 34

Department 2 = January '19 has 88, December '18 has 64, November '18 had 100

etc.


We create a column for each month and put that data in the column

11/2018

32

88


Next column

12/2018

28

64


Next column


1/2019

34

100


We do this every single month, so that means we are adding a new column with the newest data.


Issue #1

I need to calculate the difference between the current month - past month.


Column 1/2019 - Column 12/18


I then need to calculate the difference between the current month - 2 months prior


Column 1/2019 - Column 11/2018


How do I do this, knowing that the column is going to change every single month when we add the newest month column?


Issue #2

I need to create filters that show me entries that increase and decrease, based off the formulas created above.


Is this even possible?

With Airtable — or any RDBMS — whenever your process says "Every eblank days], we enter new nblank],’ you should immediately think of using a linked record for each of those tblanks].


In this case, in your table, you would define a linked-record field that links to the table. In turn, would have the following fields:




  • {Month} - the primary field, which could be a single-line text field in which you enter the month of the report in ‘MM/YYYY’ format.


  • {Dept 01 Headcount} - Integer number field.


  • {Dept 02 Headcount} - Integer number field.

    b…]


  • {Dept ## Headcount} - Integer number field.


Now, each month, instead of creating a new column.¹ you would click on the ‘+’ sign in the {Monthly Personnel Reports} field, create a new linked-record, and populate it with each department’s headcount.


That’s the easy part.


Actually, performing the ‘this v last’ and ‘this v last-plus-one’ reports isn’t all that difficult — but explaining how to do them is. So I’m going to pass the ball — to myself. Here is a previous post discussing multi-record calculations, which is what you want to do. That post includes a link to a base demonstrating four types of multi-record calculation; one of the demonstrations is




  1. Retrieving a value from the previous and previous-but-one records.



precisely your use case.


Be warned: It does take a little time to set up a base to perform calculations of this type — but once it’s in place, it will run indefinitely without your having to cut-and-paste columns or modify formulas each month


Let me know (reply to this reply, so Airtable Community will flag your response to my attention) if you have questions or problems.




  1. …which, in Airtable, wouldn’t work the way you expect it to, anyway: In Airtable, a column represents a single field as stored in multiple records. Your ‘date/value/value’ example not only includes two different types of data — date and number — it also contains values from 3 different records. A more natural way to model those data would be to have a single record include all department values for a given date.

Terms & Conditions