Skip to main content

Automating Data Summaries each month

  • January 9, 2021
  • 6 replies
  • 70 views
A
Forum|alt.badge.img+5

I have a bunch of property data I am getting each month for multiple properties.

My end goal is to build monthly summaries for each property like this:

  • Avg price for each floor plan(1br,2br,studo)
  • amount of units available at by floorplan (1br,2br,studio)

Eventually I’d like to put these calculations in some type of chart to compare these metrics at these levels.

Is there a way to run these calculations in Airtable dynamically? Maybe using scripts?

My goal is to create these charts where users can assess these trends.

Any advice would be greatly appreciated!

6 replies

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • 868 replies
  • January 9, 2021

Hi @aaron_bell

I’m no scripting guy, so people smarter than me will no doubt be able to help you with that, but imo it can be done with the Airtable functions. It depends on how you want to get the data I think.

For example, you could just group the summaries on type (1br,2br,studo) and then have the average shown for a “price” field. You also could make a simple filtered view to show the units that are available (and maybe have the sum showed for a “number of available units” field). With the chart app, you can probably make the needed chart(s) to compare these things.

But as I said, that’s the easy answer :slightly_smiling_face:

Jan - Databaser
B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • January 9, 2021

Hi @aaron_bell

I’m no scripting guy, so people smarter than me will no doubt be able to help you with that, but imo it can be done with the Airtable functions. It depends on how you want to get the data I think.

For example, you could just group the summaries on type (1br,2br,studo) and then have the average shown for a “price” field. You also could make a simple filtered view to show the units that are available (and maybe have the sum showed for a “number of available units” field). With the chart app, you can probably make the needed chart(s) to compare these things.

But as I said, that’s the easy answer :slightly_smiling_face:


I’m a coder; everything looks like a nail for my collection of hammers. And despite this, I agree.

But, it all depends on whether there are aggregations that require multiple passes or other complexities.

I would look at the count field and roll ups. Ideally you want the summaries in a new table so that reporting and views are a bit simpler to manage.

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • 868 replies
  • January 9, 2021

I’m a coder; everything looks like a nail for my collection of hammers. And despite this, I agree.

But, it all depends on whether there are aggregations that require multiple passes or other complexities.

I would look at the count field and roll ups. Ideally you want the summaries in a new table so that reporting and views are a bit simpler to manage.


I feel your pain :grinning_face_with_sweat: But with all these smart scripting experts on this forum, it’s sometimes good that someone points out that not everything has to be scripted. At least, that’s what I’m telling myself :smiling_face_with_halo:

Jan - Databaser
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • January 10, 2021

A lot depends on how you structure your data. Formula fields and rollup fields can go a long way in calculating averages and amounts.

Another issue is generating “monthly” reports. Do you generate the report each month based on all available data? Or do you want to generate a report for each month using only that month’s data? In the latter case, you need a way of determining the month for an individual record and consolidating information for that month, either through linked records or some sort of code.

A
Forum|alt.badge.img+5
  • aaron_bell
  • Author
  • Inspiring
  • 8 replies
  • January 11, 2021

A lot depends on how you structure your data. Formula fields and rollup fields can go a long way in calculating averages and amounts.

Another issue is generating “monthly” reports. Do you generate the report each month based on all available data? Or do you want to generate a report for each month using only that month’s data? In the latter case, you need a way of determining the month for an individual record and consolidating information for that month, either through linked records or some sort of code.


The complex part of this is there are multiple subsets of data that need to be analyzed each month. Airtable would need to go into the newly received data for that month and analyze it in multiple subsets on a specific timeframe

B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • January 11, 2021

The complex part of this is there are multiple subsets of data that need to be analyzed each month. Airtable would need to go into the newly received data for that month and analyze it in multiple subsets on a specific timeframe


There are three reasons we capture data:

  1. Compliance - what happened; when did it happen?
  2. Process - where do we stand; what’s next?
  3. Analytics - let’s make a decision.

Data modelling is a delicate dance that must embrace each of these key objectives. If you have a data model that cannot effectively embrace the requirements of #3, you need to fix that in the model with deference to the limitations of the database platform, …OR… you must backfill the weaknesses with script automation.

That @kuovonne - for being so young, she speaks the wisdom of a very old and deeply experienced database designer.


Terms & ConditionsAccessibility statement