Thanks in advance for any help you can offer.
I’ve been wracking my brain for the past week, trying to figure out how I can calculate my teams hours spent on a particular job type.
I’ve imported our teams hours from our time tracking software via CSV file. Right now, I have the items grouped by Client > Project. I’ve filtered by “Project contains > web” Within is also how many hours were spent on that job. These groups are able to tell me the sum of all the hours, per group. What I need to know now, is how to calculate the average amount of time spent on a certain job type, for example, “web design”.
Like I said, been trying this for quite a while, with no results. Any help is much appreciated.
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.
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.
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.