Determine how many employees the company had

[JustinDallas]

Hello Folks,

I have a problem where I would like to determine how many employees the company had on a certain day (full-time and temp).  The employees have a StartDate and TerminationDate.  Right now, I iterate over the days between the Start/TerminationDate to make a table which then hooks into the calendar.  Here is some test data:

Employees:
Load *,
EmployeeId AS '%employee_key',
Date(Date#(StartDateText,'YYYY-MM-DD')) AS StartDate,
Date(Date#(TerminationDateText,'YYYY-MM-DD')) AS TerminationDate
;
Load * Inline
[
	EmployeeId, StartDateText,TerminationDateText,EmploymentType
    'Ricky', '2018-04-10','2018-10-30', TEMP
    'Ronnie', '2018-05-03', '2018-10-10', FT
    'Bobby', '2018-06-14', '2018-07-31', FT
    'Mike', '2018-06-15', '2018-09-31', TEMP
    'Ralph', '2018-03-04', '2018-07-28', FT
    'Johnny', '2018-02-15', '2018-09-31', TEMP
]
;
DROP FIELDS StartDateText, TerminationDateText
;

EmployeeTenures:
LOAD
	EmployeeId AS '%employee_key',
    Date(Start + IterNo() - 1) AS '%fact_calendar_key',
  	Date(Start + IterNo() - 1) As 'Employee Tenure Date'
While Start + IterNo() - 1 <= End
;
LOAD EmployeeId, 
	DayStart(StartDate) AS Start, 
    DayStart(TerminationDate) AS End
Resident Employees
;

MasterCalendar: 
LOAD 
 TempDate AS %fact_calendar_key, 
 Date(DayStart(TempDate)) AS CalDate,
 YEAR(TempDate) As Year, 
 MONTH(TempDate) As Month,
 WEEKSTART(TempDate) AS WeekStart
 ;

//=== Generate a temp table of dates === 
LOAD 
 DATE(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
 MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');

When the user selects [WeekStart= 04/29/2018] there should be a count of 2 for Full-Time employees (Ronnie, Ralph) and a count of 2 for Temp employees (Ricky, Johnny).

I know that I need to use the count function:

Count([Driver Id])

I also know that I need to use the Max and Min of the Cal Date

Count( {<[Employee Tenure Date]={"=<$(Max(CalDate) >=$(Min(CalDate))"}>} [Driver Id] )

Lastly, I need to filter based on employee type, let's assume the Temp employees

Count( {<[Employee Tenure Date]={"=<$(Max(CalDate) >=$(Min(CalDate))"},[Employee Type]={'TEMP'}>} [Driver Id] )

Of course this doesn't work and gives me a big round 0.  I know I probably should be using AGGR, but me and Aggr have never gotten along.

Any help is greatly appreciated

[sunny_talwar]

Assuming you make a selection in Cal Date... this should work

Count({<EmploymentType = {'TEMP'}>} EmployeeId)

[goncalo_ricardo_pereira · Accepted solution]

Hi,

I agree with the previous answer, nevertheless if you want or actually need to use dates in your expression, you need to use this syntax in your set analysis (there is other ways to do it).

Using your example:

Count( {<[Employee Tenure Date]={">=$(=Min(CalDate)) <=$(=Max(CalDate))"}, [Employee Type]={'TEMP'}>} [Driver Id] )