17 Replies Latest reply: Jun 6, 2017 2:48 AM by Alice Wong RSS

    Count customer by product family

    Alice Wong

      Hi,

      I would like to get customer count by product family from a set of data with below criteria:

           Customer category = Cat-B

           Total Revenue by Customer >= $10000

       

      Sample data:

      Product FamilyCustomer CategoryCustomerRevenue
      ACat-AAPPLE1300
      ACat-BORANGE10000
      BCat-BPEAR11000
      CCat-AAPPLE140000
      DCat-BPAPAYA30000
      DCat-ACHERRY50000

       

      The count result I expected for the above is as below:

      Product FamilyCustomer Count
      A1
      B1
      C0
      D1

       

      My current expression below only works correctly when i filter on just 1 Product Family. When I release the filter on Product Family, it returns incorrect count results. Please help.

       

      COUNT(DISTINCT

        AGGR(

           IF (SUM ( {<$>*<CUSTOMER_CATEGORY ={"CAT-B"}>} REVENUE) >= 10000, CUSTOMER),

          CUSTOMER))

       

      Thank you!