Calculating FTE dependent on Selections

[Anonymous]

Hello,

I'm having a hard time with calculating FTE (Full-time equivalents while including selections).

In calculating FTE, depending on dimension (time) you divide hours worked by 1920 (year), 160 (month), 480 (quarter), etc.

Now, I have a situation where it is possible to have more than 1 year, so therefore, I would say, 2015 and 2016 have been selected, sum hours from both years and then divide by (1920*2).

This makes things complicated when it comes to dynamic calculations.

For instance it is currently September 2016 so if I had that year and month selected I would calculate like so : sum(hours) / 9*160

If I had both 2015 and 2016 and September I would need the FTE of Jan - Sep for both years. Therefore, sum(hours) / (9*160)*2.

I think I have the logic down I'm just having trouble bringing it to life with expression(s). Any ideas?

Thanks,
Channing

[sunny_talwar]

How about this:

Sum(hours)/((Num(Month) * 160) * Count(DISTINCT Year))

[Anonymous]

Sunny,

This works great and is very close. Now, I need to figure out how to have a rolling sum of the hours worked from the previous month.

This is where  your expression has me now (I'd like to ignore those that fall under August as it's missing data and 160 is a default value):

Thanks,

Channing

[sunny_talwar]

Well would you be able to elaborate as to what your expected output is?

[Anonymous]

Yes,

I would like to add the hours from the previous month (creating a cumulative sum) just like that which is happening under the Employed field in my last image.

So if you refer to my image February's Hours would  be 1752.08+1737.48 and Employed would be 320 (as it is in the image). If this logic was in place I'd get the total of Jan - July hours to divide by 1120 Employed

I can probably use Set Analysis to omit anything beyond July (this data is outdated, but I'd be omitting any month that hasn't occurred yet).

Does this make more sense?

[sunny_talwar]

Try this:

RangeSum(Above(Sum(hours)/((Num(Month) * 160) * Count(DISTINCT Year)), 0, RowNo()))

[MindaugasBacius]

Try using =Rangesum("4th column",rowno())

[Anonymous]

The rangesum works! Now I can omit the late rmonths with Set Analysis. Is it possible to retain these range sum values upon the selection of let's say May? So, when I select May I get 9965.19

Thanks,

Channing

[sunny_talwar]

May be like this

RangeSum(Above(Sum({1}hours)/((Num(Only({1}Month)) * 160) * Count({1}DISTINCT Year)), 0, RowNo())) * Avg(1)

[Anonymous]

The numbers don't quite line up. I'm sort of at a loss.