Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
atlantis_shahri
Contributor III
Contributor III

Finding the closing No. of working employee each year.

I want to find out the no. of working employee each year. But problem is all I'm getting the no. of currently active employee each your. I need the closing employee no. each year. Any kind of help or suggestions will be appreciated.

 

Year Month Emp_Code Joining_Date Discontinue_Date No. of Employee No. of Emp. Last yr 
     00
2011AprCM000014/2/2011 11
2016FebCM000022/1/20161/31/201722
2017OctCM0000310/23/2017 33
2018JunCM000046/2/201811/2/201844
2018NovCM0000611/10/2018 55
2018NovCM0000711/20/2018 66
2018NovCM0000511/10/201811/11/201877
2019MayCM000085/5/2019 87
2019JunCM000096/12/2019 97
Labels (2)
6 Replies
Vegar
MVP
MVP

You will need to explain what output you are expecting from the provided data. 

It could be a great help if you could create and add an desired output table to this post.

atlantis_shahri
Contributor III
Contributor III
Author

I am trying to find the working employee in every year. Above table should resulting into something like this .

Year Working Employee No.
20111
20162
20172
20184
20196
Vegar
MVP
MVP

I see. You can solve this by using interval match against your master calendar. By doing this you can get the no of employees  at any choosen date, not only December 31.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

atlantis_shahri
Contributor III
Contributor III
Author

I tried that. All I end up like this

 

Year 

Emp_Code Joining_Date Discontinue_Date Start End Count(DISTINCT Emp_Id) RangeSum(Above(TOTAL Count(DISTINCT Emp_Id), 0, RowNo(TOTAL)))  
      90 
2011CM000014/2/2011   11 
2016CM000022/1/20161/31/20171/1/201712/31/201712 
2017CM0000310/23/2017   13 
2018CM000046/2/201811/2/20181/1/201812/31/201814 
2018CM0000511/10/201811/11/20181/1/201812/31/201815 
2018CM0000611/10/2018   16 
2018CM0000711/20/2018   17 
2019CM000085/5/2019   18 
2019CM000096/12/2019   19 

EmployeeList:
Load Emp_Id,Emp_Code,Emp_Categ_Id,Is_SaleForce,Emp_Name,Father_Name,Mother_Name,
Birth_Date,Sex as Sex_Id,Maritial_Status as Status_Id,Religion as Religion_Id,Email_Id,Mobile_No,
if(Is_Active=0,'Left Employee',if(Is_Active=1,'Active Employee', Is_Active)) as [Employee Status],
Blood_Group,Nationality,date(Joining_Date,'MM/DD/YYYY' ) as Joining_Date,

Date(Discontinue_Date,'MM/DD/YYYY') as Discontinue_Date,
age(Today(),Birth_Date) as Emp_Age,
Year(Joining_Date) as [Emp Joining Year],

age(Today(),Joining_Date) as [Service Length],
Day(Joining_Date) as Day,Year(Joining_Date) as Year,Month(Joining_Date) as Month,

'Q' & Ceil(Month(Date(Today()))/3) as Quarter,
date(monthstart(Joining_Date), 'MMM-YYYY') as MonthYear,
'Week ' & Ceil(Day(Date(Joining_Date))/7) as WeekYear;


SQL SELECT *
FROM "IAAS_ERP_GHC".dbo."hrm_trans_employee_mst" where Is_Deleted=0;

OrderLog:
LOAD Date(Start,'MM/DD/YYYY') as Start,
Date(End,'MM/DD/YYYY') as End
INLINE [
Start, End
'01/01/2016', '12/31/2016'
'01/01/2017', '12/31/2017'
'01/01/2018', '12/31/2018'
'01/01/2019', '12/31/2019'
];

inner Join IntervalMatch ( Discontinue_Date )

LOAD Start, End
Resident OrderLog;

Vegar
MVP
MVP

See attached QVW for example on how to do it.

clipboard_image_0.png

Brett_Bleess
Former Employee
Former Employee

Shahriar, did Vegar's last post and sample work for you?  If so, please be sure to circle back and use the Accept as Solution button on his post to give him credit and let others know this worked.  If you are still working on things, leave us an update, and if you figured something else, consider posting that and then mark that as the solution so others will know what you did.

I am including a Design Blog post on IntervalMatch as well, might help with that part of things:

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.