7 Replies Latest reply: Feb 28, 2018 4:06 PM by Digvijay Singh RSS

    Help with Aggr

    David Le

      I have searched on this topic and have not found a solution

       

      I am trying to get an average of active employees by month and gender

       

      My data looks like this

       

      ReportMonthEmpIDActive FlagNew Hire FlagGender
      01/2018110F
      01/2018210F
      01/2018301F
      01/2018410M
      01/2018501M
      02/2018110F
      02/2018200F
      02/2018310F
      02/2018400F
      02/2018510M
      02/2018601M
      02/2018701M
      02/2018801M

       

      What I am trying to do is get the average  count of Active Flag + New Hire by month and gender.

       

      I have this and this works by Month.

       

      AVG(Aggr(count( {<ActiveFlag= {'1'}>} EmployeeID) + count( {< NewHireFlag= {'1'}>} EmployeeID), ReportMonth))

       

      In my bar chart I have only gender as a dimension  but it doesn't do the calculations right.

       

      My expected result based on the data above would look like this.

       

      Average Female active employees for the both month = 2.5

      Average Male active employees for both months = 3

       

      Please help