Number of employees at given date

[kevbrown]

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

[tresesco]

Could you post a sample qvw with representative data?

[ciaran_mcgowan]

Hi Kevin,

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

[MK_QSL]

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)

[kevbrown]

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

[MK_QSL]

Simply use my script and add

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

Use Date filed as List Box