Showing Month-Over-Month Data, Same Table

[andrew8]

Hey All, I really appreciate the help.

I have a table tracking operations data by day. The shop loads the data each day via a form and I track on a dashboard showing production over the last 30 days. Each day the report rolls forward 1 day. It’s a great simple view of our world.

I’m trying to show month over month numbers on the same dashboard. In my mind I need to pull the production from the day’s record from 28 days prior to every record in the table so the dashboard can sum the data from the [28 days ago] field. However, I can’t seem to figure out how to pull this data into each record in the same table. It needs to happen automatically.

Optionally, if there’s a way to setup the dashboard to pull a different filtered set of data on a single element unaffected by the page filter, that might work too.

Thanks for the help.

[TheTimeSavingCo]

Hi Andrew, any chance you could provide an example of what you’d like the final product to look like? Would love to help but don’t really follow I’m afraid

[andrew8]

You bet. Here is the interface as it stands:

I’d like to add a total that shows the total doors made from the previous 30 day period (month-over-month). The only way I can think to get that data displaying correctly is if I add a field to my table that pulls the total doors made from the record in the same field dated 28 days (4 weeks) prior to that record. That way when the page filter is pulling in the records from the last 30 days I’m able to total up the data from the preceding 30 days since the data from those records has been pulled into these current records…if that makes sense. Essentially a record for 10/29/22 would show all the data from 10/29/22 but would also pull the total doors made from the record for 10/1/22. 10/28/22 would have the production data from that day, but also show just the total doors made from 9/30/22. And so on through all the records.

[TheTimeSavingCo]

Apologies in advance, I don’t know if I’m really following what you’re trying to do so I may be way off the mark here

What if we:

1. Created a field that outputs the day difference between the record’s date and today’s date
2. Created a new table called “Summary” or some such and made a link between the original table and the new table
3. Created an automation that would run once a day that would
   • Look for all the records in the original table that had a value of 30 or lower from the field we created in step 1
   • Create a new record in the Summary table with today’s date and link the results from the previous step to this record
4. In the Summary table, we created a rollup field to sum the number of doors

This way you’d have one record per day in the Summary table, each linked to the records representing the last 30 days in the original table, with a rollup of the number of doors made in those days, and it’d be automated?

[andrew8 · Accepted solution]

Hey Adam, thank you so much for the help. I see what you’re doing there. I’ll probably use that elsewhere, but I did find a super simple solution.

I didn’t realize you could have multiple page filters in the interface. By adding a second filter that operates independently of the other filter I created I simply have one filter pulling the data from records dated in the last 30 days. Then I have another filter pulling the records on or after 60 days ago and not in the last month. That nets out to the previous 30 days before the original reporting period (the last 30 days). I duplicated the same elements from the last 30 days and then linked those to the new filter.
The result is elements showing both time periods right next to each other. The numbers are such that I can do the math in my head to see what my month-over-month variances are. Super simple. Airtable is so awesome.

Thank you again!

[Daniel_VK]

Hi, first post here. I'm joining late to the party but maybe this can help other people looking for the same.

If you add a column with a formula that extracts the month, then you can add a filter that asks for said information. Here's how I did it:

1. Make sure you have a "Date Created" column. I use created time, but you could type it in.

2. For then new column, here's the formula that extracts the month: DATETIME_FORMAT({Date Created},'YYYY-MM')

3. In your filter, add a filter that asks for the name of the column (in my case, "Month") and requests a value. I added a subtitle so that I remember how to use it.

The last step as you said, would be to have TWO different filters, with two sets of big numbers or pie charts or lists, one linked to each filter. This has the advantage of letting you see ANY couple of months, not just the previous and the one before.

Hope it helps!