Equivalent Headcount Calculation

[#1]

Hi Everybody,

I need to come up with a way to calculate Equivalent Headcount, assuming an employee normally works 40 hours per week. So if someone works 48 hours in one week, they are equivalent to 1.20 employees. If the next week they work 35 hours, they are the equivalent to 0.75 employees.

The actual headcount I'm using is simply count( DISTINCT [Employee ID]) but I'm struggling to find a way that works when looking at Equivalent Headcount by month.

An example of the data I'm using is attached. Each week (with the exception of week ending April 26th) the actual headcount is 3 employees. But with the hours being worked every week varying so much, changes from as high as 3.647 to as low as 1.888.

Anyone have any ideas? I'm thinking I'll have to use AGGR somehow?

Thanks,

Justin

Actual Hours Worked:

Row Labels	emp1	emp2	emp3
29-Mar-15	46	42	58
05-Apr-15	24	31	31
12-Apr-15	33	40	53
19-Apr-15	32	31	24
26-Apr-15	27	49	0
03-May-15	18	39	40
10-May-15	39	41	40
17-May-15	32	37	44
24-May-15	31	37	18
31-May-15	32	30	37

Equivalent Headcount:

Row Labels	emp1	emp2	emp3	Equiv HC
29-Mar-15	1.150	1.040	1.456	3.647
05-Apr-15	0.600	0.769	0.775	2.144
12-Apr-15	0.825	1.010	1.326	3.160
19-Apr-15	0.800	0.775	0.594	2.169
26-Apr-15	0.675	1.213	-	1.888
03-May-15	0.450	0.975	1.000	2.425
10-May-15	0.975	1.015	1.006	2.996
17-May-15	0.800	0.916	1.105	2.821
24-May-15	0.783	0.919	0.450	2.151
31-May-15	0.800	0.757	0.913	2.470

[swuehl]

This depends on what you want to analyze.

Personally, I only know the distinction between headcount (counting employees by number of heads, regardless if the work only part time) and full time equivalent (FTE), where you count an employee only as 1/2 if he has a contracted work load 50% of standard (e.g. 20 hours per week instead of 40 hours/week), to somehow count number of employees.

I am not sure how you want to define headcount / FTE when looking at the actual hours worked, like in your example.

Maybe you are more interested in work load?

[robert_mika]

I'm not sure if I got the question correct.

Do you need to replicate the Excel Pivot/table in Qlikview.

If yes please see attached

[#4]

Maybe I'm misinterpreting what you said, but it seems like we're both talking about the same thing, just with different names?

I'm trying to look at different ways to measure efficiency, like sales per employee, or sales per hour worked.

[#5]

Hi Robert.

Your attached QlikView file is blank.

I'm trying to measure efficiency different ways, like sales per employee or sales per hour worked.

So if one person works 80 hours in one week, they are essentially doing the work of two people. I'd like to be able to show the different between "Sales per Actual Number of Employees" and "Sales per Equivalent Number of Employees." If we had sales one week of $1,000 and two employees worked 55 hours and 35 hours respectively, the calculations would be (assuming 40 hours per regular work week)

Sales per Actual Number of Employees = $1,000 / 2 = $500 per actual headcount

Sales per Equivalent Number of Employees = $1,000 / ( (55/40) + (35/40) ) = $444 per equivalent headcount

[robert_mika]

Try to open now

[swuehl]

Or maybe like this for a average calculation of your ratio.

[#8]

What I ended up doing was creating a table that uses hours worked per month (something like NetWorkDays * 😎 and then divided the total hours by that. It's not very slick but it works I guess.

Thanks for the help guys.

[Anonymous]

Hello Friend

 I am start a new warehouse but I do not know how many staff I will need. Please advise how I calculate the required number of staff.

Thanks & best reagards