Trying to find average time per job type

In your screenshot, where it says “Sum”, you can click on that and change it to say “Average”. Does that work for you?

Unfortunately that wouldn’t work as it would only calculate the average within one project. I’m looking for the average amongst all projects within a certain job type.

Ah okay, then you would need to group by “Job Type” to get that.

So currently, you’ve got it grouped by “Client” then “Project”. You would add a 3rd grouping after that: “Job Type”.

If that’s too much grouping for you to have on one view, you could create an entirely new view that could be simply grouped by Job Type.

The only problem there is that the time tracking software we use doesn’t label by “Job Type”. The way I’ve combatted this is by making two tables with the same dat a, and filtering one for “Project name contains > web” and the other “Project name contains > Identity”. But then I’m still at a loss for how to take the average from there.

You get the average by grouping your records in the way that you want, and then you can average each group.

The only other way to get an average is by manually linking your desired records to the same exact record in another table. Then, from that other table, you can create a ROLLUP field that gives you an average of the linked records. That would probably be your best way of accomplishing this, if you don’t have the ability to group your records.

You may also be able to write a JavaScript script to help you find the average that you’re looking for, but I think the linked table method is probably easier.

Where does the insanity end. :winking_face:

Um, how about a simple script that performs a precise aggregation?

We really just need formula functions that can tap into those summary fields that already appear on the screen. All those summary fields are RIGHT THERE!! Visible on the screen!! But Airtable gives us absolutely no way to access them in our formulas.

It’s clear that you answered the question and had the data model represented the nature of the question, you’d have earned a quick check mark for this response. Unfortunately, data doesn’t always cooperate with basic summation and aggregation features.

Given that the data model isn’t ideally suited for averages by groups, @Cole_Hamel has to either mimic the shape of the data to use that approach, or use a Script Block to compute the aggregations.

Couldn’t agree more! Frustrating, but I’ll keep truckin to see if I can find a solution.
Thank you immensely for your help and input @ScottWorld @Bill.French.

If you manually link your records to an another table, you can do what you want to do!

True, it would work, but the promise of database technology and the quest to develop analytics is largely based on a reasonable expectation that the automation golden goose will begin to pay dividends. In my view, once transaction data is captured, nothing thereafter should ever depend on manual interactions because humans do this so poorly.

We don’t have a clear sense for how many groups and clients would need to be linked manually, but if it’s more than 7 (that’s the magic number which I’ll happily expound on if asked), it’s going to result in disappointment.

One must ask (where manual tasks are required) - what is the cost of forgetfulness? In the world of business analytics, it could be sizeable when executive-level decision-making is based on incomplete data.

Right, but Airtable isn’t a fully-relational database system like FileMaker is.

I’m simply giving @Cole_Hamel solutions that are based on how Airtable currently functions.