1 Reply Latest reply on Mar 28, 2013 2:34 PM by Joshua Milligan

    distinct count across cumulative weeks

    Vincent Atwood

      I have a trend workbook with Trends for various key indicators, such as distinct # of customers ordering per week, distinct # of orders per week, etc.  I am trying to look for the distinct count to show up only once, at first order, and not be counted again in any following week.  The idea is to see how many new customers there are, by week.


      For example, my current trend shows unique # of customers per week

      (Row name 'Distinct count of CORP_BSNSS_ID', and Axis name '# CID's' in the attached Packaged Workbook):

      Week1 = 7

      Week2 = 28

      Week3 = 81


      Sum     = 116


      However, the actual # of unique customer for the entire 3 weeks = 100 and not 116.  This is because customers who bought in Week1 may have also bought in Week2 or 3, and so on.


      I'm looking to see only the 100 counted only on the first week they made a purchase.  So the trend should look something like:

      Week1 = 7

      Week2 = 24

      Week3 = 69


      Sum     = 100 (not sure what the actual counts are - but should = 100)


      The same goes with distinct # of orders and the rest of the key indicators, but if I can get this one I can probably figure out the rest.


      Hopefully someone can help.  Thanks.


      Note - if you create a cross tab and total the rows - it shows the total distinct as being 100 - see attached screenshot.