Reply
Highlighted
Visitor
Posts: 4
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)? 

Blue Belt
Posts: 176
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'
Blue Belt
Posts: 176
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'
Visitor
Posts: 4
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.

Blue Belt
Posts: 176
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'
Visitor
Posts: 4
Registered: ‎12-03-2015

Re: Reporting FTE's

Thanks! I really like the possibility here - can you expand on the idea of a calendar dataset?

 

My dataset looks something like this, but with a few more code values:

PR Date, EE #, Job Code, Worked-In Site Code, Worked-In Program Code, Pay Code, Hours worked

 

An employee can utilize multiple pay codes, and utilize any number of combination of other codes; meaning they can have any number of lines of data (0-10+), for each payroll.

 

What would the join look like?  What would the data set look like after the join?

 

Announcements
Customize your Dojo Handle! You can update your Dojo screen name by clicking on My Settings. click here! Thanks!