3 Replies Latest reply: Jul 20, 2017 5:56 AM by Fabio Castanheira RSS

    Calculating AVG with condition

    Fabio Castanheira

      Hello everyone,

       

      I have the following (simplified) table :

       

      DAYEMPLOYEE

      NB_CALLS

      01/01/2017John19
      02/01/2017John8
      03/01/2017John13
      01/01/2017Jane21
      02/01/2017Jane15
      03/01/2017Jane10
      04/01/2017Jane11
      .........

       

      The business requirement is to have an expression that calculates the average number of calls per day and employee (no problem) but with the following condition : a day must only be counted if the number of calls is strictly superior to 10.

       

      So the 2nd and 6th line of the table should be "ignored", giving us an average of :

      (19+13)/2 = 16 for John

      (21+15+11)/3 = 15.7 for Jane

       

      Do you think that this is possible to calculate in an expression, so that the data model doesn't need to be changed ? I have made some tests with the aggr() function but I can't get the correct results...

       

       

      Thank you for your help, don't hesitate to ask if you need more details about my issue.