topic Re: Employee Headcount in QlikView

Employee Headcount

Fri, 09 Mar 2012 15:53:08 GMT

I am trying to do an employee headcount based on a hire date and inactive date

This is the formula I am using. Note: Active Date is just today() * 2 to put it into the future.:

sum(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1))

My dimension is CalendarMonthEnd.

My issue is that it is summing it for every day in the month. So Month ending 10/31/2011 for 1 employee should return 1. It currently returns 31. How can I go abount doing this? Would Aggr work?

Employee Headcount

Fri, 09 Mar 2012 16:06:53 GMT

by look of it... i guess using avg or count can solve ur issue

Employee Headcount

CELAMBARASAN — Fri, 09 Mar 2012 16:38:59 GMT

Hi,

Try this

sum(Aggr(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1),EmployeeField))

Celambarasan

Re: Employee Headcount

Fri, 09 Mar 2012 18:48:25 GMT

This was correct, I tried that just before you posted and it returned the right number.

On another note, this is a pivot with the month end along the top. Any idea how I can change just the total field to show the average month head count?

Nevermind, found the secondarydimensionality ( ) function