Hi All.
I have a requirement where i need to show month over month active customers.
I have explained my scenario below:
Say for Jan-2013 i have data as below
Cust_Id,Month,Status
1,Jan-2013,Active
2,Jan-2013,Active
3,Jan-2013,Active
4,Jan-2013,Active
4,Jan-2013,InActive
5,Jan-2013,Active
6,Jan-2013,Active
6,Jan-2013,InActive
7,Jan-2013,Active
So for Month Jan-2013 i have Active Customers: 7 (Note if a customer is entered as active it is considered as active for that particular month, here in case of id 6 will be considered active for Jan month)
Feb-2013
Cust_Id,Month,Status
4,Feb-2013,Active
9,Feb-2013,Active
11,Feb-2013,Active
34,Feb-2013,Active
56,Feb-2013,Active
32,Feb-2013,Active
5,Feb-2013,InActive
32,Feb-2013,InActive
for Month Feb-2013 i have calculate active customers as: (7+6)-1=12 (here -1 is because customer id 6 is inactive in previous month and is not activated in the feb month)
Similarly next month Mar-2013
Cust_Id,Month,Status
20,Mar-2013,Active
21,Mar-2013,Active
22,Mar-2013,Active
23,Mar-2013,Active
24,Mar-2013,Active
35,Mar-2013,Active
for Month Mar-2013 : (7+6+6)-2: 17 (here in Customer Id 5 and 32 is still inactive from previous month)
I need to show my result as
| Month | Active Customers |
|---|
| Jan-2013 | 7 |
| Feb-2013 | 12 |
| Mar-2013 | 17 |
| |
Please help me out to solve this logic in qlikview..
I have upload the sample data file..
Thanks
