topic Employees HeadCount in QlikView

Employees HeadCount

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

Hi there -

I went through few discussions on this title but nothing helped hence creating a new one.

I have data like this -

Emp Id	Start Date	End Date

I want an output like this -

Month-Year	Count of Employees

*** I have start dates from 1992 but I have to build a report of employee count from Jan-2015 (Which will consider the count before 2015 as well).

Thanks in advance

-Sneh

Re: Employees HeadCount

kaushiknsolanki — Mon, 19 Jun 2017 09:39:25 GMT

Hi,

You will have to use the interval match function.

Regards,

Kaushik Solanki

Re: Employees HeadCount

sunny_talwar — Mon, 19 Jun 2017 09:39:40 GMT

Use Loops in the Script‌ (while loop) to create a single date field from a date range or you can use an IntervalMatch to do this.

Re: Employees HeadCount

Anil_Babu_Samineni — Mon, 19 Jun 2017 09:44:41 GMT

First you need to make it as single date

Load EmpId, Date(StartDate + IterNo() - 1) as FinalDate

While Date(StartDate + IterNo() - 1) <= EndDate;

Load EmpId, StartDate, EndDate;

Then create Straight table like

Date(FinalDate,'MMM-YYYY') as Calculated Dimension and label called MonthYear

Expression is Count({<Date = {">=$(=MakeDate(2015, 01, 01)) <=$(=Max(Date))"}>}EmpId)

Re: Employees HeadCount

ashish_2511 — Mon, 19 Jun 2017 10:00:36 GMT

Hi Thanks for the quick response .

Below is the script I've written -

Employee:

Load *,
if(not exists(Endtemp), today(), Endtemp) as [End Date]
;

LOAD
//'Employee' as Table_Name,
Upper(REGION) as REGION,
ANALYST_KEY,
LOCAL_ID,
ANALYST_NAME,
START_DATE as [Start Date],
Month(START_DATE) as Start_Month,
Year(START_DATE) as Start_Year,
END_DATE as Endtemp,
Month(END_DATE) as End_Month,
Year(END_DATE) as End_Year

FROM
ANALYST.qvd
(qvd);

Calendar:
Load
Date,
Year(Date) & '-' & Month(Date) as Yyyymm,
Month(Date) as Month,
Year(Date) as Year;
Load
Date(MinDate+IterNo()) as Date
while MaxDate - MinDate >= IterNo()
;
LOAD
MIN([Start Date]) as MinDate,
Max([End Date]) as MaxDate
Resident Employee;

Interval:
Inner join (Employee)
intervalMatch (Date)
Load distinct [Start Date], [End Date]
resident Employee
;

In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.

and the numbers don't seem to match with the actuals numbers.

Where m I wrong?

Re: Employees HeadCount

ashish_2511 — Mon, 19 Jun 2017 10:02:02 GMT

Snehasis Patnaik Jun 19, 2017 6:00 AM (in response to Sunny Talwar )

Hi Thanks for the quick response .

Below is the script I've written -

In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.

and the numbers don't seem to match with the actuals numbers.

Where m I wrong?

Re: Employees HeadCount

sunny_talwar — Mon, 19 Jun 2017 10:03:25 GMT

Don't see anything wrong in the script... may be share some sample data with the expected output and we can try running your script to check what might not be working here.

Re: Employees HeadCount

arvind_patil — Fri, 30 Jun 2017 10:25:25 GMT

Dear Snehasis,

It may help you.

//SET DateFormat='DD.MM.YYYY';

EmpData:

Load * INLINE [

EmpID, Leave, Date Start, Date End

100, Leave, 01.01.2017, 10.01.2017

100, Leave, 15.01.2017, 20.01.2017

101, Leave, 10.01.2017, 20.01.2017

102, Leave, 18.01.2017, 30.01.2017

103, Leave, 25.01.2017, 31.01.2017

105, Leave, 30.01.2017, 31.01.2017

];

Final:

LOAD [EmpID], [Leave], [Date Start] as Sdate , [Date End] as Edate,

Date([Date Start]+iterno()-1) as Date

resident EmpData while [Date Start]+IterNo()-1 <=[Date End];

drop table EmpData;

Thanks ,

Arvind Patil