Dear All,
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
Rolling 12
| CALMONTH | ACTIVE | Retain | New | Reactivated |
|---|
| 201710 | 3 | 2 | 1 | 0 |
Rolling 6
| CALMONTH | ACTIVE | Retain | New | Reactivated |
|---|
| 201710 | 3 | 2 | 1 | 0 |
Monthly
| CALMONTH | ACTIVE | Retain | New | Reactivated |
|---|
| 201710 | 2 | 1 | 1 | 0 |
