3 Replies Latest reply on Aug 27, 2018 12:38 PM by swaroop.gantela

    Distinct Count over date period range

    Samuel Rodriguez
      DateSales
      CustomerId
      2018-01-018000cust01
      2018-01-017000cust02
      2018-01-019000cust02
      2018-01-019500cust03
      2018-02-013500cust01
      2018-02-014500cust02
      2018-02-016500cust03
      2018-02-014000cust04
      2018-02-018500cust03
      2018-03-014500cust01
      2018-03-019000cust04
      2018-03-016000cust02
      2018-03-013000cust05

       

       

      I am trying to get the distinct count over a period of 12 months on a rolling 12. 

       

      In this example, I have 3 months of example data. For the example, I would like to get the distinct count of CustomerId on a rolling 2. That means that February's distinct count on a rolling 2 should be 4 (cust01, cust02, cust03, and cust04) and March's distinct count on a rolling 2 should be 5 (cust01, cust02, cust03, cust04, cust05). The final result is the sum of Sales on a rolling 2 divided by the distinct count on a rolling 2 for the respective month.

       

      For the Sales (since I am not dealing with distinct count), I am using window_sum() but I get stuck when calculating the distinct count of the CustomerId since the window_sum(countd([CustomerId]), -1, 0) function will bring up duplicate counts (e.g. 3 + 4 = 7).

       

      • So, the question is how do I avoid these duplicates and do a distinct count over a date period?