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)?
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
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?
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.
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.
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?