New column - Same formula?


#1

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?


#2

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

In this case, in your [Main] table, you would define a linked-record field that links to the [Monthly Personnel Reports] table. In turn, [Monthly Personnel Reports] 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.
    […]
  • {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.