Running headcount per month year

[daveesjohn]

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

[MayilVahanan]

HI

Try like below

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

[daveesjohn]

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

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/QlikView-App-Dev/Reference-DateTime-for-calculating-occupancy/m-p/1282...

https://community.qlik.com/t5/QlikView-App-Dev/Count-records-per-day-or-week-between-two-dates-Conta...

[Phills]

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;

[anat]

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

Or 

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