# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:
EmployeeId AS '%employee_key',
Date(Date#(StartDateText,'YYYY-MM-DD')) AS StartDate,
Date(Date#(TerminationDateText,'YYYY-MM-DD')) AS TerminationDate
;
[
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:
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
;
DayStart(StartDate) AS Start,
DayStart(TerminationDate) AS End
Resident Employees
;

MasterCalendar:
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 ===
DATE(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
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)

• ### time

1 Solution

Accepted Solutions
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).

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

2 Replies
MVP

## Re: Determine how many employees the company had

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

`Count({<EmploymentType = {'TEMP'}>} EmployeeId)`
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).

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