Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

Determine how many employees the company had

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

Labels (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: Determine how many employees the company had

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] )

 

2 Replies

Re: Determine how many employees the company had

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

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

Re: Determine how many employees the company had

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] )