Reply
Highlighted
Welcome to Dojo
Posts: 3
Registered: ‎12-03-2015

Reporting FTE's

I need to create a formula in beast mode to calculate FTE's.  My dataset includes each PR an employee is paid.

 

If an employee works 80 hours in one pay period, the FTE = 80.00hours worked /80.00 hours available to work = 1.00 for that period.

If that same employee does not work at all the next period, their FTE = 0.00 for the period.  But year to date, the FTE would = 0.50 (80.00/160.00 ytd workable hours).

 

I built a beast mode:

SUM(`Actual Hours`)/(COUNT(DISTINCT `Payroll Date`)*80)

 

But this doesn't work in the above scenario, since there is not a line of data for the 2nd payroll; it calculates the distinct PR dates as 1, and the formula results in an FTE of 1.00.

 

Is there a good way to combat this & have the calculation look at the distinct count in the entire data set (not just the filtered down view)? 

Purple Belt
Posts: 155
Registered: ‎07-17-2015

Re: Reporting FTE's

if each employee works 80 hours on a bi-weekly pay period, there will be 24 pay periods in a year

 

then, can your formula be

 

SUM(`Actual Hours`)/(24*80)

 

now, if the pay period and work hours require to be met are different between employees, then you can bring a table that identify each employee's workable hours so that you can join that value to run against the "actuals hours' by employee. does it make sense?

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Purple Belt
Posts: 155
Registered: ‎07-17-2015

Re: Reporting FTE's

[ Edited ]
 
Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Welcome to Dojo
Posts: 3
Registered: ‎12-03-2015

Re: Reporting FTE's

Thanks, but unfortunately that doesn't work for this because it would always consider all pay periods, not just those that have occurred so far, or those that have been filter down if the manager wanted to see only a month or a quarter, etc.

Purple Belt
Posts: 155
Registered: ‎07-17-2015

Re: Reporting FTE's

In that case you could create a dataset that contains a Calendar with the values of payroll hours and payroll dates. once that is created you could join it to your actuals, that way it will work the way you are describing.

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Announcements
Important! We are aware of the issue in trying to post in Dojo, please see this thread for a temporary workaround here! Thank you for your patience we hope to have this resolved soon!