3 Replies Latest reply: Jun 18, 2013 4:55 AM by Abhijit Bansode RSS

    How to visualize unique values from a sliding timeframe?

      Hello, I have a problem where I need help from you experts.

       

      I want to count unique customerId within a range of months and show this in a graph.

      Let's assume I want to show the unique customerId within a sliding 3 months 'window'.

       

      I have a table looks like below.

       

      Year-Month CustomerId
      2012-01  A01
      2012-01  B01
      2012-02  A01
      2012-02  A01
      2012-02  C01
      2012-02  B01
      2012-03  A01
      2012-03  B01
      2012-03  B01
      2012-03  D01
      2012-03  E01
      2012-04  B01
      2012-04  A01
      2012-04  B01
      2012-04  B01
      2012-04  C01
      2012-05  D01
      2012-05  A01
      2012-05  B01
      2012-05  C01
      2012-05  A01
      2012-06  B01
      2012-06  B01
      2012-06  C01
      2012-06  A01

       

      Based upon above chart, the result I want to present is as below.

       

      Year-Month Unique-CustomerId
      2012-01  2
      2012-02  3
      2012-03  5
      2013-04  5
      2013-05  5
      2013-06  4

       

      'window' for 2012-01 is 2012-01
      'window' for 2012-02 is 2012-02 and 2012-01
      'window' for 2012-03 is 2012-03, 2012-02 and 2012-01
      'window' for 2012-04 is 2012-04, 2012-03 and 2012-02

      and so on...

      any ideas how to make above happen? I have been struggeling with Range-functions, aggr-functions but not managed to get it all together.