topic Re: Number of employees at given date in QlikView

Number of employees at given date

kevbrown — Wed, 22 Jun 2016 08:25:36 GMT

Easy question but I've had a mental block. I'm looking to find out the current number of employees I have at any chosen date. The fields I have are CalMonth, CalYear, CalDay (Make up my calendar controls), Person ID, Leaving Date and Starting Date. Starting Date links to calendar, so whichever starting date is selected I want to know how many people were employed on that date

So Leaving date must be null or greater than the date selected in the calendar and Starting date must be less than the date selected. Easy?

Thanks

Re: Number of employees at given date

tresB — Wed, 22 Jun 2016 08:29:03 GMT

Could you post a sample qvw with representative data?

Re: Number of employees at given date

ciaran_mcgowan — Wed, 22 Jun 2016 08:31:43 GMT

Hi Kevin,

You could try IntervalMatch (IntervalMatch) combined with something like if(Len(LeavingDate)=0, Today()+1, LeavingDate)

Re: Number of employees at given date

MK_QSL — Wed, 22 Jun 2016 08:49:20 GMT

Employee:
Load EmployeeID, StartingDate, If(Len(Trim(LeavingDate))=0,Date(YearEnd(Today())),LeavingDate) as LeavingDate Inline
[
  EmployeeID, StartingDate, LeavingDate
  1, 10/01/2016,
  2, 12/01/2016, 25/04/2016
  3, 15/02/2016, 01/06/2016
  4, 24/02/2016,
  5, 01/03/2016, 15/05/2016
  6, 14/04/2016,
  7, 20/04/2016, 18/06/2016
  8, 01/05/2016, 22/06/2016
  9, 11/05/2016, 12/06/2016
  10, 22/05/2016,
  11, 01/06/2016,
  12, 15/06/2016,    20/06/2016
];




Temp_MinMaxDate:
Load Min(StartingDate) as MinDate, Today()+1 as MaxDate Resident Employee;


Let vMinDate = Num(Peek('MinDate',0,'Temp_MinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'Temp_MinMaxDate'));


Drop Table Temp_MinMaxDate;


Calendar:
Load Date, Year(Date) as Year, Month(Date) as Month ,Week(Date) as Week;
Load
  Date($(vMinDate) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


IntervalMatch(Date)
Load StartingDate, LeavingDate Resident Employee;

Now create a straight table.

Dimension

Date

Expression

COUNT(Distinct EmployeeID)

Re: Number of employees at given date

kevbrown — Wed, 22 Jun 2016 08:50:46 GMT

I just want to create a Text box with the figure in it

Re: Number of employees at given date

MK_QSL — Wed, 22 Jun 2016 08:53:41 GMT

Simply use my script and add

COUNT({<Date = {'$(=Max(Date))'}>}Distinct EmployeeID)

Use Date filed as List Box