Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Count with aggregate in Qlik Sense

Hi all,

I have this employee table:


Person IDStart DateEnd Date

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!

Thanks in advance.


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

Tags (1)
1 Reply
Not applicable

Re: Count with aggregate

Your expression will not work (regardless of the context here), because it is incomplete.

Read this: Aggregations and Function Classes