I have the data in below format where P stands for Passive;N stands for new;R stands for Reacivated;A stands for Retain and Active is summation of R,A,N. When we are looking 12 month or 6 month count we need to consider the customer lastest status and the show the count.
For example:- suppose a customer 1 is Reactivated in Jan 2017 and Retain in Sept 2017 then in last month the count of Reactivated Customer should be zero whereas Count of Retain Customer is 1 as this is the lastest status in last 12 month
CALMONTH
%Customer
Status
201611
1
P
201612
1
201701
1
R
201702
1
201703
1
201704
1
201705
1
201706
1
201707
1
201708
1
201709
1
A
201710
1
201611
2
P
201612
2
201701
2
R
201702
2
201703
2
201704
2
201705
2
201706
2
201707
2
201708
2
R
201709
2
A
201710
2
A
201611
3
201612
3
201701
3
201702
3
201703
3
201704
3
201705
3
201706
3
201707
3
201708
3
201709
3
201710
3
N
by using the above data i want create 3 tables i.e. Rolling 12, Rolling 6 and Current
