Display a sum, average, or median from projects completed in the last 90 days

[Seth_Crabtree]

Hi folks,

I’m an Airtable rookie trying to do something fairly complex… thanks in advance for the help!

My main table has a list of projects (films) with several additional fields, the most relevant being Completion Date, Revenue, Team Hours Dedicated, and Quality Score.

I’m trying to create another table (or series of tables) with a monthly scorecard which automatically calculates the following metrics on the first day of each month, only for projects which were completed in the last 90 days:

• Total Revenue
• Average Team Hours Dedicated per project
• Median Quality Score

I’ve been trying various combinations of IF statements, DATEADD, etc., but am making little headway.

Any ideas?

[Databaser]

Hi @Seth_Crabtree and welcome to the community!

Do you want to keep a log of past monthly scores? If not, you could just make a view with project that were completed in the last 90 days and use the sum, average and median functions in the bar below you records (or next to your grouping titles).

[Seth_Crabtree]

Thanks @Databaser!

I do want to keep a log of past monthly scores, but I think the simplest way to do that is going to be for me to follow your recommendation above (which worked beautifully) and manually record the values in a separate spreadsheet each month (which I have to do anyways to compile my scores with those of other departments).

If I was trying to keep a monthly log in AirTable, though, what kinds of functions & views do you think I’d need to pull it off? No need for a detailed explanation, just trying to get a sense for the order of magnitude of complexity I’d be dealing with if I decided to go that route.

Thanks again for the advice, and the quick response!

[Databaser]

To log -since you can’t use the sum, average and median results on a group level, you could create a linked field to link the records to the correct month. In the newly created table (eg “Months” > check out the app “Eazyfields” to simply add a Month and Year field) you could then add rollup fields to calculate the sum, average and median. Then you could use some apps (eg: “chart” or “summary”) to visualise some things.

There are other ways possible, but that seems like the easiest way to do it.