topic Re: Running headcount per month year in App Development

Running headcount per month year

daveesjohn — Thu, 01 Dec 2022 09:41:29 GMT

Hi,

I want to create a chart that shows the as of / running total headcount of employees based on their hiring date, and exclude resigned employees.
assuming I have a sample data below:

EMP_ID	DATE_HIRED	DATE_RESIGNED
212	01/03/2022
213	01/03/2022
214	02/04/2022
215	02/04/2022	03/01/2022
216	03/04/2022
217	03/05/2022
218	03/05/2022	04/02/2022
219	04/05/2022
220	04/05/2022

the result should be

MONTH_DATE	HEAD_COUNT
JAN 2022	2
FEB 2022	4
MAR 2022	6
APR 2022	7

Thank you

Re: Running headcount per month year

MayilVahanan — Tue, 15 Nov 2022 04:04:05 GMT

Try like below

Exp: Rangesum(Above(Count(DISTINCT EMP_ID), 0, RowNo(Total)))

Re: Running headcount per month year

daveesjohn — Tue, 15 Nov 2022 05:05:16 GMT

Hi @MayilVahanan

Thanks for your feedback, it's working, but I forgot what If we have additional field such as "DATE_RESIGNED" wherein we only need to count the Active employees as of the current running date and year

Re: Running headcount per month year

MarcoWedel — Tue, 15 Nov 2022 21:49:40 GMT

maybe helpful:

https://community.qlik.com/t5/QlikView-App-Dev/Reference-DateTime-for-calculating-occupancy/m-p/1282211/highlight/true#M401977

https://community.qlik.com/t5/QlikView-App-Dev/Count-records-per-day-or-week-between-two-dates-Contar-registros/m-p/1018329/highlight/true#M641751

Re: Running headcount per month year

Phills — Tue, 25 Jun 2024 20:51:54 GMT

you'll be able to solve your problem this way:
Note: I modified the data for some tests

Employees: LOAD EMP_ID , SEX , DATE#(DATE(DATE_HIRED,'MM/YYYY'), 'MM/YYYY') AS DATE_HIRED , DATE#(DATE(DATE_RESIGNED,'MM/YYYY'), 'MM/YYYY') AS DATE_RESIGNED INLINE [ EMP_ID , SEX , DATE_HIRED , DATE_RESIGNED 212 , F , 03/01/2022 , 213 , F , 03/01/2022 , 214 , M , 04/02/2022 , 215 , M , 04/02/2022 , 01/03/2022 216 , M , 04/03/2022 , 217 , M , 05/03/2022 , 218 , M , 05/03/2022 , 02/04/2022 219 , M , 05/04/2022 , 220 , F , 05/04/2022 , 05/05/2022 ]; hc_temp: LOAD EMP_ID ,SEX ,DATE_HIRED as Date ,Sum(1) as Hiring RESIDENT Employees GROUP BY DATE_HIRED,EMP_ID,SEX; Join(hc_temp) LOAD EMP_ID ,SEX ,DATE_RESIGNED as Date ,Sum(1) as Resignation RESIDENT Employees WHERE Len(Trim(DATE_RESIGNED))>0 GROUP BY DATE_RESIGNED,EMP_ID,SEX; DROP TABLE Employees; HC: LOAD * ,Resignation/HeadCount as Turnover ; LOAD * ,RangeSum(Peek(HeadCount), HeadCount_tmp) as HeadCount ; LOAD Date ,SEX ,Alt(Hiring,0) as Hiring ,Alt(Resignation,0) as Resignation ,Alt(Hiring,0) - Alt(Resignation,0) AS HeadCount_tmp RESIDENT hc_temp; DROP TABLE hc_temp;

Re: Running headcount per month year

anat — Wed, 26 Jun 2024 02:38:34 GMT

Rangesum(Above(Count({<dr-={""}>}DISTINCT EMP_ID), 0, RowNo(Total)))

Rangesum(Above(Count({<dr-={"null"}>}DISTINCT EMP_ID), 0, RowNo(Total)))