topic Re: Opening Head Count - Employees - URGENT Help required in QlikView

Opening Head Count - Employees - URGENT Help required

vikasmahajan — Thu, 20 Jun 2013 09:33:39 GMT

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

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Mon, 13 Oct 2014 11:39:38 GMT

Does any body design this type of requirement

Re: Opening Head Count - Employees - URGENT Help required

jonathandienst — Mon, 13 Oct 2014 12:14:35 GMT

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...

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Thu, 20 Nov 2014 09:35:08 GMT

Do you have full scirpt please send me

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Thu, 20 Nov 2014 11:21:38 GMT

Please can you give me full script I am not able to get it.

Vikas

Re: Re: Opening Head Count - Employees - URGENT Help required

salto — Thu, 20 Nov 2014 12:38:08 GMT

Hello,

this may help you - please check and let me know.

Regards.

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Fri, 21 Nov 2014 04:31:44 GMT

Thanks Mr.SALTO I will check N Get Back you soon.

Vikas

Re: Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Tue, 13 Jan 2015 12:10:25 GMT

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

Re: Re: Opening Head Count - Employees - URGENT Help required

salto — Tue, 13 Jan 2015 12:24:03 GMT

Hi Vikas,

the "grade" filed is not available, I have made the chart with S_Active as the dimension. Hope this helps!

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Tue, 13 Jan 2015 12:41:13 GMT

Thanks

I will check and get back you

Vikas

Re: Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Wed, 14 Jan 2015 04:16:00 GMT

I really appreciate your help Thanks.

Jose I really require when user select 2013-14 then closing head count of employee should match with Apr-2014-15

987 will be opening how to do this please give any idea ?

Vikas

Re: Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Fri, 16 Jan 2015 10:40:56 GMT

hello

Can you please explain me how Tdata and Employee tables will be join which is common key ?

// Join the tables

Join (T_Data)

LOAD * Resident Employees;

Vikas

Re: Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Fri, 16 Jan 2015 10:42:07 GMT

I have attached emp.xls can you please create qvw and send me

Thanks

Re: Re: Opening Head Count - Employees - URGENT Help required

salto — Fri, 16 Jan 2015 11:02:36 GMT

Hello Vikas,

I see no attached file - however I cannot understand your requirements. CAn you please explain them a little bit more?

Regards.

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Fri, 16 Jan 2015 11:12:01 GMT

Hii

Please see I have open new threat Head Count Analysis - Employee ( Opn + Gross Addtions -Resignations = Closing)

Requirement is when user select Year/Month/Qtr following chart should display, Suppose when user will select

Mar-2014 head count will be 480 , When user will select Apr-2014 so 480 will be in opening head count like this I want to display in chart

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

I have attached Emp excel file

Please can you create qvw n send me.

Thanks

Vikas

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Fri, 16 Jan 2015 11:20:20 GMT

Mr.Jose

Following is requirement.

User Will SElect Year/Qtr/Month

DOJ -> Date of joins and DOL -> Date of Leaving and S_Active -> Active/In-Active fields to be considered to calculate this head count.

Let me know If you know more information.

Thank u very much.

Vikas

Re: Opening Head Count - Employees - URGENT Help required

salto — Fri, 16 Jan 2015 12:35:20 GMT

Hi,

I have answered in the new thread.

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Tue, 20 Jan 2015 10:36:12 GMT

Mr.Jonathan Can you give full script Please help me

Vikas

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Tue, 20 Jan 2015 10:40:06 GMT

I think this script will resolve my issue please help me to get desired result.

Vikas

Re: Opening Head Count - Employees - URGENT Help required

vikasmahajan — Tue, 20 Jan 2015 11:29:09 GMT

//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;

1. From.... ??????????

2. Join which condition ?????

Vikas