Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I have empno , empname, doj,date_of_resign fields
how i can show opening headcount/gross additions/attrition/closing head count
GRADE | Opening Headcount | Gross Additions | Attrition | Net Additions | Closing Headcount |
ASSOCIATE | 193 | 3 | 0 | 3 | 196 |
STAFF | 74 | 3 | 0 | 3 | 77 |
SUPERVISOR | 80 | 0 | 0 | 0 | 80 |
TRAINEE | 32 | 1 | 1 | 0 | 32 |
WORKERS | 95 | 0 | 0 | 0 | 95 |
Total | 474 | 7 | 1 | 6 | 480 |
Please any one have idea please give guide lines also I want to calculate Attriation rate company level how I do this ?
Thanks in avanace
Vikas
Does any body design this type of requirement
Hi
Something like this
// set start date of headcount table
Let vStartDate = Date#('2013/01/01');
// set monthly range for headcount table
T_Data:
LOAD PeriodID,
Date(AddMonths($(vStartDate), PeriodID - 1), 'YYYY-MM') As MonthYr
;
LOAD RecNo() As PeriodID
AutoNumber 24;
//Load employee data
Employees:
LOAD grade,
empno,
empname,
MonthStart(doj) As StartDate,
MonthStart(date_of_resign) As EndDate
From .....
// Join the tables
Join (T_Data)
LOAD * Resident Employees;
// Identify active employees
T_Data2:
LOAD grade,
PeriodID,
MonthYr,
empno,
If(StartDate < MonthYr And MonthYr <= EndDate, 1, 0) As Active
Resident T_Data;
//Construct headcount table
HeadCount:
LOAD *,
GrossAdditions - Attrition As NetAdditions,
OpeningBalance + GrossAdditions - Attrition As ClosingBalance
;
LOAD PeriodID,
MonthYr,
grade,
Count(Distinct If(Active = 1, empno)) As OpeningBalance,
Count(Distinct If(MonthStart(StartDate) = MonthYr) empno) As GrossAdditions,
Count(Distinct If(Active = 1 And EndDate = MonthYr, empno)) As Attrition
Resident T_Data2\
Group By PeriodID, MonthYr, grade
;
//Cleanup
DROP Table T_Data, T_Data2;
(check for syntax errors like missing commas etc)
HTH
Jonathan
Edit - added missing Group By...
Do you have full scirpt please send me
Please can you give me full script I am not able to get it.
Vikas
Hello,
this may help you - please check and let me know.
Regards.
Thanks Mr.SALTO I will check N Get Back you soon.
Vikas
Dear Mr.Jose,
Find attachment of my live data with qvw I have try your solution can you please help me to
how to implement in Live data.
Thanks
Vikas
Hi Vikas,
the "grade" filed is not available, I have made the chart with S_Active as the dimension. Hope this helps!
Thanks
I will check and get back you
Vikas