How to make a chart based on Date-in and Date-out time

[xiaoyang]

I have HR source data including Employee number, onboarding time and departure time (if null means still on job) . 

Now I want to make a chart, show the on job counts each month, year as filter, and months as dimension, chart type: bar chart or line chart both ok. 

How can I make it ? 

[Taoufiq_Zarra]

@xiaoyang  can you share a sample data and the expected output ?

[MayilVahanan]

Hi @xiaoyang 

Try like below

https://community.qlik.com/t5/New-to-Qlik-Sense/Interval-Match-Help-In-Qlik-Sense/m-p/36972

One thing is, make departure null value with Today in order to avoid any missing data while using intervalMatch.

if(Len(Trim(DepartureTime))>0, Today(), DepartureTime) as DepartureTime

Hope it helps

[xiaoyang]

Hi,  Zarra, 

Thank you so much for your reply, here I attached the source data, and expected bar chart. 

Here the Hire Date means the date that the Employee on boarding the company. The Termination Date means the Employee leave the company. The bar chat need to show how many employees in the data each month. So need a logic on both HIredate and Termination Date. 

The dimension of the bar chat is month, and filter is year. 

Thanks!

[xiaoyang]

Thank you Mayil for your helping reply. I think I still has problem on how to build the logic .  The numbers need to shows in the chart cannot be directly from the source data . 

See my attached source data and the requested output.  

Thanks again for your help, 

Xiaoyang / Maggie

[MayilVahanan]

Hi @xiaoyang 

Try like below

Load Min([Hire Date]) as MinDate
FROM
[D:\Qlik\personnal\Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Let vMinDate = Num(Peek('MinDate'));

Let vMaxDate = Num(Today());

[Master Calender]:

Load Date(Dates, 'MM/DD/YYYY') as Dates, Month(Dates) as Month, Year(Dates) as Year, MonthName(Dates) as MonthYear;

Load $(vMinDate) + IterNo()-1 as Dates

AutoGenerate 1 while $(vMinDate) + IterNo()-1 <= $(vMaxDate);

Interval:
LOAD [Employee Num],
[Hire Date],
[Termination Date],
[Employee Status]
FROM
[D:\Qlik\personnal\Data.xlsx]
(ooxml, embedded labels, table is Sheet2);
Load Distinct [Employee Num] , [Termination Date] as Dates, 'Termination' as Flag Resident Interval where not isnull([Termination Date]);
Load Distinct [Employee Num], [Hire Date] as Dates, 'Hire' as Flag Resident Interval;

Front end:

Dim: Month

Hire: Count({<Flag={'Hire'}>}DISTINCT [Employee Num])

Termination: Count({<Flag={'Termination'}>}DISTINCT [Employee Num])

[xiaoyang]

Thank you Mayil Vahanan for your help, let me study and then let you know, 

Thanks / Maggie