1 Reply Latest reply: Nov 16, 2017 10:58 AM by Youssef Belloum

# Count with aggregate in Qlik Sense

Hi all,

I have this employee table:

 Person ID Start Date End Date 1674 11/1/2014 1/8/2015 1674 1/9/2015 12/31/9999 1675 1/5/2016 12/31/9999 1681 11/1/2017 1/11/9999

I want to calculate the numbers of employees in current period (lets say Jun 2016 until Jun 2017) and numbers of employees in past period (before Jun 2016)

I create the following table with this expression:

=aggr(min([Start Date]),[Person ID])

Now, I want to count the number of employees in current and past period:

# EE Current period=3

# EE Past period=2

I was trying to do something like:

# EE Past period= count({<'Start Date of the aggregate function above'={<=1/6/2016}>}[Person ID])

# EE Current period= count({<'Start Date of the aggregate function above'={<=1/6/2017}>}[Person ID])

Is it possible? I'm really struggling with this!

--------EDIT

Let me edit or change the question:

Suppose I have this table:

PersonID     StartDate     EndDate

1674           11/1/2014     1/4/2017

1674           1/5/2017       12/31/9999

1675           1/5/2016       12/31/9999

1681           11/1/2017     12/31/9999

1682           11/2/2015     12/31/9999

Current period is the date from Jun 2016 to Jun 2017, and previous period is all dates before Jun 2016

I want to count all the employees in current and past year:

# EE Current period=4 (1674,1675,1682,1681)

# EE Past period=3 (1674,1675,1682)

When I do a count(distinct [PersonID]), how can I ensure that I am taking the person ID with the min start date? In this case I want to be sure that I am counting the Person ID 1674 with start date 11/1/2014