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