Qlik Community

Qlik Sense App Development

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

danimelo1
Not applicable

Count with aggregate in Qlik Sense

Hi all,

I have this employee table:

   

Person IDStart DateEnd Date
167411/1/20141/8/2015
16741/9/201512/31/9999
16751/5/201612/31/9999
168111/1/20171/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])

foto.png

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

Tags (1)
1 Reply
YoussefBelloum
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