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

    Count with aggregate in Qlik Sense

    daniel melo

      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