Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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] )
Assuming you make a selection in Cal Date... this should work
Count({<EmploymentType = {'TEMP'}>} EmployeeId)
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] )