Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cworkman2015
Contributor III
Contributor III

Calculating FTE dependent on Selections

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

11 Replies
sunny_talwar

How about this:

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

cworkman2015
Contributor III
Contributor III
Author

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):

rollingSum.PNG

Thanks,

Channing

sunny_talwar

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

cworkman2015
Contributor III
Contributor III
Author

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
Partner
Partner

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

Screenshot_3.png

cworkman2015
Contributor III
Contributor III
Author

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

rollingSum.PNG

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)

cworkman2015
Contributor III
Contributor III
Author

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

rollingSum.PNG