Average daily active employees calculation
Howdy y'all.
We use Quickbase for an HRIS. Within the HRIS, there's an employees table that contains fields for the hire date, termination date, and status.
An example employee could have a hire date of 08/01/2020, a termination date of 08/01/2023, and a status of 'Inactive'.
I've been asked by HR to calculate turnover. The formula they provided requires me to first calculate average daily active employees for a given time frame.
The way I see it, if I just had that one example employee, that report would look something like this:
| Date | # of active employees |
| 07/30/2023 | 1 |
| 07/31/2023 | 1 |
| 08/01/2023 | 0 |
| 08/01/2023 | 0 |
Average daily active employees report would look at each date and count the number of employees whose hire date was on or before that date, and their termination date is on or after that date or null, and their status on that date was active. In this example, the average daily active employees for the given time frame is 0.5 (2 divided by 4).
I would then need to create a summary report based on that report? And the summary would divide the number of employees who left during that time frame by the average daily active employees during that time frame. That's the formula I've been given by HR.
I do actually have a question...how can I create that table above? The way I'm thinking through it, I'll need a table that contains every possible date so that the user input for a start and end date can be set and used in the calculation. But to my knowledge, Quickbase doesn't have an obvious way to do this.
Thoughts?
A question for you. How often do they want to see this report?
If it is infrequent, once a year.... I would not build this in Quickbase. I would export the HR data to Excel and massage it there to produce your answer.
If the answer is that it has to be in Quickbase.
- Create a Table [Daily Active Employees].
- Create a scheduled Pipeline that runs every night creating one new record in the table with the date, # of Active Employees and the # of Employees that left that day.
You can now Create a Summary Report on that table where you specify the date range. Use report variables to calculate your averages.
