Skip to main content

Hi I'm a Plus plan subscriber so starting to explore AT more in depth.

I have a built a base with a JOBS table that lists jobs by job number, and using various currency, numerical and formula fields, delivers a profit per hour figure for each job. It does this by dividing the total job profit by the number of hours spent on the job.

A second table called TIME LOGGING is updated with forms submitted by staff and tags their time to a job. These individual time logs are rolled up in the JOBS table to deliver the profit per hour figure. The next part is where I need help.

How can I now deliver a PROFIT PER HOUR PER STAFF MEMBER figure which takes into account that time they have spent on each job? On some jobs, several members of staff log time, so the figure must be proportional to the time they spent on the job.

E.g. If the staff member logged time to two jobs, one of which was just them, and one of which was part of a team, this would calculate as follows:

Job 1: Profit per hour is $100 based on 2 hours total time spent on a $200 job. Staff member has booked 2 hours to this job.

Job 2: Profit per hour is $125 based on 10 hours total time spent on a $1250 job. Staff member has booked 4 hours to this job.

Staff members profit per hour is ($100*2 + $125*4) / 6 = $116.67

How can I achieve this?

You'll need a third table for staff members only to do this, and here's how you'd set it up:

Link to base

Lemme know if you have any questions about set up

You'll need a third table for staff members only to do this, and here's how you'd set it up:

Link to base

Lemme know if you have any questions about set up


Thanks for your response Adam, I got some advice yesterday which solved this for me, and the base is exactly as you have laid out, so that is reassuring. I'm stuck with how to dynamically filter for date periods though. How would you do this?

 

Thanks for your response Adam, I got some advice yesterday which solved this for me, and the base is exactly as you have laid out, so that is reassuring. I'm stuck with how to dynamically filter for date periods though. How would you do this?

 


Hmm, could you tell me more about this date periods thing, perhaps an example?  Not sure I follow, sorry!

Hmm, could you tell me more about this date periods thing, perhaps an example?  Not sure I follow, sorry!


So for example, in this screen in Xero, I can select a date range and it will bring up all items in that date range. 

I can do this for the previous x amount of days by having fields in the staff members table that roll up fields from the time logging table on the condition that they were in the past x amount of days. This is useful.

However, I would like to be able to select a custom date range in case I want to compare say quarter to quarter for 2022, looking at 1 quarter at a time. So I would enter a date range Jan 01 2022 - Mar 31 2022 to see Q1.

So for example, in this screen in Xero, I can select a date range and it will bring up all items in that date range. 

I can do this for the previous x amount of days by having fields in the staff members table that roll up fields from the time logging table on the condition that they were in the past x amount of days. This is useful.

However, I would like to be able to select a custom date range in case I want to compare say quarter to quarter for 2022, looking at 1 quarter at a time. So I would enter a date range Jan 01 2022 - Mar 31 2022 to see Q1.


I'm not confident I understand you though as, with what you've mentioned, view filters would be ideal for solving that.  Given your familiarity with Airtable you would already know about those though

Is it that you want rollups for each of those ranges, so, for example, if you wanted to view stuff by quarters, you'd have one record per quarter and the appropriate rollup fields?  If so, I'd create a formula field that would output the date range types that I wish to have these rollups for, e.g. "Q1 2023, Q2 2023, May 2023", etc and then use an automation to paste this comma separated list into a linked field to a table called "Summary" or some such

I'm not confident I understand you though as, with what you've mentioned, view filters would be ideal for solving that.  Given your familiarity with Airtable you would already know about those though

Is it that you want rollups for each of those ranges, so, for example, if you wanted to view stuff by quarters, you'd have one record per quarter and the appropriate rollup fields?  If so, I'd create a formula field that would output the date range types that I wish to have these rollups for, e.g. "Q1 2023, Q2 2023, May 2023", etc and then use an automation to paste this comma separated list into a linked field to a table called "Summary" or some such


I've abandoned this after realising I can have fields filter for last 7 days / 30 days / 90 days / 365 days. This enough granularity for me. Thanks though.

 

Reply


Terms & Conditions