Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Count active workers per month

Hello,

I have this problem. I have a list of contingen workers, each worker has a starting date and end date.

   

Start DateEnd Date
12/27/20161/17/2017

I would like to create a Bar Chart for the year 2017 (Jan to Dec) and count how many active workers I have in that specific month. I can use/count distinct the ID or Name of the worker. The problem is that in some cases I have a worker that started in Oct 2016 and will end his contract in Sep 2018 and in that case the worker will be active each month during 2017, but I have other cases where the worker started in Dec 2016 and will end his contract in Jan 2017, so I can only count that worker as active in Jan 2017 but not the rest of the month of 2017.

Any idea? I am ataching a sample data.

Best,

Ignacio

1 Reply
antoniotiman
Honored Contributor III

Re: Count active workers per month

Hi Ignacio,

may be this

Table:
LOAD * FROM
https://community.qlik.com/servlet/JiveServlet/download/1361353-299394/Contingent%20Worker%20Sample....
(ooxml, embedded labels, table is Sheet1);

LOAD DISTINCT [Contingent Worker],MonthName([Start Date]+IterNo()-1) as Month,
Year([Start Date]+IterNo()-1) as Year
Resident Table
While [Start Date]+IterNo() <= [End Date]
;

Regards,

Antonio

Community Browser