1 Reply Latest reply: May 22, 2014 10:18 PM by Sergey Makushinsky RSS

    Aggregate then Count with Condition - How to do this?

      Hi,

       

      I have two input tables, as below:

       

      Sales Table
      DateRegionSalesPersonSales
      01/04/2014R1SP13
      02/04/2014R1SP16
      02/04/2014R4SP21
      02/04/2014R4SP37
      23/04/2014R1SP123
      23/04/2014R4SP35
      23/04/2014R2SP414
      23/04/2014R2SP512
      23/04/2014R3SP63
      01/05/2014R1SP112
      02/05/2014R4SP31
      03/05/2014R1SP16
      03/05/2014R4SP215
      03/05/2014R4SP36
      03/05/2014R2SP434
      03/05/2014R2SP54
      04/05/2014R4SP32
      05/05/2014R4SP26
      05/05/2014R3SP614
      06/05/2014R1SP123
      07/05/2014R4SP331
      08/05/2014R4SP336

       

      Target Table
      MonthRegionTarget
      Apr-14R12
      Apr-14R23
      Apr-14R32
      Apr-14R44
      May-14R12
      May-14R23
      May-14R33
      May-14R44

       

       

      I would like to achieve a report table with Month-to-Date performance like this:

      Month-to-Date
      RegionSalesPerson Count (with >=20 sales in MTD total)Target% Achieve
      R11250%
      R21333%
      R3030%
      R42450%

       

      What formula can I use for SalesPerson Count in order to achieve this?

       

      Thanks in advance.