topic Re: Calculating FTE dependent on Selections in QlikView

Calculating FTE dependent on Selections

Anonymous — Thu, 08 Sep 2016 15:56:02 GMT

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

Re: Calculating FTE dependent on Selections

sunny_talwar — Thu, 08 Sep 2016 19:10:55 GMT

How about this:

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

Re: Calculating FTE dependent on Selections

Anonymous — Fri, 09 Sep 2016 15:24:30 GMT

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

Re: Calculating FTE dependent on Selections

sunny_talwar — Fri, 09 Sep 2016 15:36:02 GMT

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

Re: Calculating FTE dependent on Selections

Anonymous — Fri, 09 Sep 2016 15:45:14 GMT

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?

Re: Calculating FTE dependent on Selections

sunny_talwar — Fri, 09 Sep 2016 16:03:35 GMT

Try this:

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

Re: Calculating FTE dependent on Selections

MindaugasBacius — Fri, 09 Sep 2016 16:07:36 GMT

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

Re: Calculating FTE dependent on Selections

Anonymous — Fri, 09 Sep 2016 18:21:22 GMT

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

Re: Calculating FTE dependent on Selections

sunny_talwar — Fri, 09 Sep 2016 19:06:46 GMT

May be like this

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

Re: Calculating FTE dependent on Selections

Anonymous — Mon, 12 Sep 2016 19:34:42 GMT

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

Re: Calculating FTE dependent on Selections

sunny_talwar — Mon, 12 Sep 2016 23:42:07 GMT

So I am guessing that the last column is not the expected result? What exactly are you expecting to see?

Re: Calculating FTE dependent on Selections

Anonymous — Tue, 13 Sep 2016 16:55:31 GMT

The second column from the right are the values I need to see. I need to see the ratio of Hours to Employed Hours (Hours / Employed) with a rolling sum from the numbers above.

The first three columns are correct. However, if I select May I will not get the right Hours / Employed ratio because it is not taking into account the rolling sum (the previous month's hours accumulated). I want to be able to select May and still retain the correct hours and employed hours respectively 9965.19 and 800 without having to include the other selections (essential it performs a rolling sum whether or not the other periods are selected)

Thanks,

Channing