Count with aggregate in Qlik Sense

danimelo1 — Thu, 16 Nov 2017 15:50:42 GMT

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!

Thanks in advance.

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

Re: Count with aggregate

YoussefBelloum — Thu, 16 Nov 2017 15:58:25 GMT

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

Read this: Aggregations and Function Classes

topic Re: Count with aggregate in App Development

Count with aggregate in Qlik Sense

Re: Count with aggregate