Calculations based on data in chart Table for FTE

[jamesjj]

Is it possible to base a calculation based on the info that is displayed in a Table? I'm trying to calculate FTE used from info that is displayed in a table. These change based on a date filter. 

There are 3 variables used in the calc; # of people X , days worked and hours recorded. 

The idea is to calculate hours available (people x days worked x 😎 - Hours worked. This result divided by 8. Then this result divided by days worked. Then this result subracted from # of TM's. This should be the total FTE used over any given time frame (day/week/month etc). In this example above, I have 3 FTE available, but only used .76.

Possible?

[Anonymous]

Hi @jamesjj ,

Have trouble to follow. Please share some sample data and expected result to help us better understand the logic.

Best Regards,

Jay

[jamesjj]

Hi Jay, I'll try, here goes 🙂

Data is stored in this table;

And currently able to display this;

Days worked changes based on filter;

In my example, 22 days worked in June. TM's also changes based on filter.

So I would like to multiply 22 days, by 3 team members, by 8 hours a day which should equal 528 available hours. Then subtract the 134 worked from the 528 available, which equals 393.

The 393 is then divided by 8 to equal 49. The 49 is then divded by the # of days worked (which is 22), which would equal 2.24. This 2.24 is what was still available to use, which when subtracted from the 3 team members, means we only required .76 FTE to complete the work. So 3 available, .76 used. 

This somewhat complex calc allows FTE calculation on a day/week/month/year basis.

So the following fields do not exist, but need to somehow be calculated at stored;

- Total available hours (TM's x Days worked)

- Available hours (total hours available - total hours worked) then,

- Available hours / 8 (normal work day hours)

- Available FTE (above calc / number of days worked)

- Used FTE (Available FTE - # of TM's).

Hope this helps 🙂