0 Replies Latest reply: Sep 29, 2017 4:33 PM by Mike Lutomski RSS

    RangeSum of Unique Customer IDs where customers appear in multiple months

    Mike Lutomski

      I'm trying to sum the unique count of customers in a 3 month range.  The customers can appear in more than one month.

      I am getting the number I am looking for in a KPI by using the following:

      Count( Distinct {<[Date] ={'>=$(=AddMonths(Max([Date]),-2))<=$(=Max([Date]))'}>} [Customer ID])

       

      When I try to use the same calculation in a table and get the sum of the three months by doing the following:

      RangeSum( Above( Count( Distinct {<[Date] ={'>=$(=AddMonths(Max([Date]),-2))<=$(=Max([Date]))'}>} [Customer ID]),0 ,3) )

       

      I end up counting a Customer ID for each month it appears.

       

      In the table below, for the count(distinct formula, I get 12, which is what I want

      when I do the RangeSum(above, I get 14.  for 3/1/2017, I want the RangeSum(Above()) to equal 12.

       

      I would like to see the following results in my table.

      Date          Customer Count

      1/1/2017    2

      2/1/2017    3

      3/1/2017    12

       

      I tried using the sum(aggr before the rangesum(above, but still got the same results.

       

      Any help is appreciated.  Thanks in advance.

      Customer IDDate
      13/1/2017
      23/1/2017
      33/1/2017
      43/1/2017
      53/1/2017
      63/1/2017
      73/1/2017
      83/1/2017
      93/1/2017
      103/1/2017
      11/1/2017
      11

      1/1/2017

      122/1/2017
      12/1/2017