Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
0 | 0 | |||||
2011 | Apr | CM00001 | 4/2/2011 | 1 | 1 | |
2016 | Feb | CM00002 | 2/1/2016 | 1/31/2017 | 2 | 2 |
2017 | Oct | CM00003 | 10/23/2017 | 3 | 3 | |
2018 | Jun | CM00004 | 6/2/2018 | 11/2/2018 | 4 | 4 |
2018 | Nov | CM00006 | 11/10/2018 | 5 | 5 | |
2018 | Nov | CM00007 | 11/20/2018 | 6 | 6 | |
2018 | Nov | CM00005 | 11/10/2018 | 11/11/2018 | 7 | 7 |
2019 | May | CM00008 | 5/5/2019 | 8 | 7 | |
2019 | Jun | CM00009 | 6/12/2019 | 9 | 7 |
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.
I am trying to find the working employee in every year. Above table should resulting into something like this .
Year | Working Employee No. |
2011 | 1 |
2016 | 2 |
2017 | 2 |
2018 | 4 |
2019 | 6 |
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.
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))) | |
9 | 0 | |||||||
2011 | CM00001 | 4/2/2011 | 1 | 1 | ||||
2016 | CM00002 | 2/1/2016 | 1/31/2017 | 1/1/2017 | 12/31/2017 | 1 | 2 | |
2017 | CM00003 | 10/23/2017 | 1 | 3 | ||||
2018 | CM00004 | 6/2/2018 | 11/2/2018 | 1/1/2018 | 12/31/2018 | 1 | 4 | |
2018 | CM00005 | 11/10/2018 | 11/11/2018 | 1/1/2018 | 12/31/2018 | 1 | 5 | |
2018 | CM00006 | 11/10/2018 | 1 | 6 | ||||
2018 | CM00007 | 11/20/2018 | 1 | 7 | ||||
2019 | CM00008 | 5/5/2019 | 1 | 8 | ||||
2019 | CM00009 | 6/12/2019 | 1 | 9 |
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;
See attached QVW for example on how to do it.
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