Counting Active Employee for 1 year previous

[pthai]

Hello everyone,

Ex:

EMP ID	Year	Date	Type
1	2015	1/2/2015	hire
2	2015	7/20/2015	hire
3	2016	2/3/2016	hire
2	2016	9/2/2016	terminate
3	2017	7/3/2017	terminate
2	2017	8/22/2017	hire
2	2017	10/12/2017	terminate

The result should be:

Year	Count of Hire Employee	Count of Terminate Employee	Count of Active Employee from 1 year previous
2015	2	0	2
2016	1	1	2
2017	0	2	0

For Count of Hire Employee and Count of Terminate Employee,

• we need to check max date of each year of type 'hire/terminate', then count

For Count of Active Employee from 1 year previous,

• we need to check max date of current and previous year of type 'hire', then count

 How can we count as result below ? Can we do it in creating measure expresion? Please help!

I also applied https://community.qlik.com/t5/New-to-Qlik-Sense/Active-employee-count-for-specific-year/td-p/1289285...

, but not work

[pthai]

Hello!

Anyone? Pleasee

[Almen]

I'm not sure I understand your output correctly.  You show one hired EMP in 2017 and one active from 2015, yet your sum of active EMP is only 1? Also, do you re-use the EMP_ID because ID 2 is hired, terminated and hired again.

[pthai]

Thanks for your response.

We calculate for the current and previous year

For example: 

In 2017 and previous is 2016,

We have: 

• EMP ID 3 who hired in 2016, but terminated in 2017. So the result is 0.
• EMP ID 2 who terminated in 2016, but hired in 2017. So the result is 1.

The total will be 1.

[vikasmahajan]

hi,

you need to use peek & previous function to calculate active employee which quite similar like calculating inventory balance :

see following links :

https://community.qlik.com/t5/QlikView-App-Dev/Opening-Head-Count-Employees-URGENT-Help-required/m-p...

https://community.qlik.com/t5/New-to-Qlik-Sense/Active-employee-count-for-specific-year/td-p/1289285

https://community.qlik.com/t5/New-to-Qlik-Sense/Opening-and-Closing-Balances-per-month/td-p/1379271

https://community.qlik.com/t5/New-to-Qlik-Sense/Extract-daily-data-from-cumulative-data-table-today-...

Search for Employee Headcount

Hope this helps you

Thanks

Vikas

[pthai]

Hi Vikasmahajan,

Thank you for your input. I already research your link, but still stuck 😞