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: 
ashish_2511
Creator
Creator

Employees HeadCount

Hi there -

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

I have data like this -

Emp IdStart DateEnd Date

I want an output like this -

Month-YearCount 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

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You will have to use the interval match function.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

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.

Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ashish_2511
Creator
Creator
Author

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?

ashish_2511
Creator
Creator
Author

                        

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?

sunny_talwar

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.

arvind_patil
Partner - Specialist III
Partner - Specialist III

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