How to visualize unique values from a sliding timeframe?

[#1]

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.

[AbhijitBansode]

See the attached app (edit script).

Hope it suffice your requirement.

Regards,

Abhijit

[#3]

Hi, thank you for the quick response. It seems not to work out the way I want it to. The last month (06) ends up with 5 unique customerId. It should be 4.

[AbhijitBansode · Accepted solution]

My mistake, I assumed window period for each month is that month plus previous all months of the year.

But, it is current month plus previous two months.

I have made the changes accordingly.

PFA.

Regards,

Abhijit