Find number of previous months

[#1]

Hi,

I am trying to calculate the number of previous months a certain customer has been buying something.

I have a table

CustID

YearMonth

Bought (yes/no)

Now, for each CustID and YearMonth I want to know how many preceding months this customer/YearMonth combination did buy anything.

To complicate even further, if the customer did not buy for two months but then started to buy again, it should still count as the customer is still a buying customer during this period.

So if he bought in January but not in Feb and Marech and then again in April -

April should show that he bought for 4 months.

I am thinking that this is probably solved in the script, but having a hard time to find the right solution.

Anyone?

[Nicole-Smith]

What does your data look like?  Could you post a few sample lines of data?

[#3]

Something like this. But with dates instead of month numbers - but the problem is the same. In this example below

CustID 4 will have a break as a buying customer after month 2, while customer 3 will not have a break after month 2.

CustID 1 and 2 is buying all months.

The result I am looking for is tabbed to each row and not part of the data

CustID;Month;Bought    

1;1;Y                    1

1;2;Y                    2

1;3;Y                    3

1;4;Y                    4

1;5;Y                    5

1;6;Y                    6

1;7;Y                    7

1;8;Y                    8

1;9;Y                    9

1;10;Y                    10

2;1;Y                    1

2;2;Y                    2

2;3;Y                    3  

2;4;Y                    4

2;5;Y                    5

2;6;Y                    6

2;7;Y                    7

2;8;Y                    8

2;9;Y                    9

2;10;Y                    10

3;1;Y                    1

3;2;Y                    2

3;5;Y                    5     (how month 3 and 4 will look like is not important as they are not active during those months)

3;6;Y                    6

4;1;Y                    1

4;2;Y                     2   

4;6;Y                    1

4;7;Y                    2

4;8;Y                    3

4;9;Y                    4

4;10;Y                    5

[#4]

Hi Peter,

Something like this might help solve your problem.  Using Peek I flagged cases where a customer's last order month was 3 months ago or more, then I created a counter field which resets itself at 1 at each customer change OR flag.  If your data set uses YearMonth, you'll probably want to Autonumber that field, also this approach assumes you have a record for a Customer/Month with a bought flag of 'N' in your actual data,  and ordering the data properly will matter if you take an approach like this.

Regardless hopefully this gets you closer to your solution.  Have a good day. 

Temp:

LOAD CustID,

     Month,

     Bought

FROM

PreviousMonthQuestion.xlsx

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN (Temp)

LOAD

CustID,

Month,

IF(IF(Peek(CustID) = CustID,Month - Peek(Month),0)<=3,0,1) as BuyingCustFlag

RESIDENT Temp

WHERE Bought <> 'N';

LEFT JOIN (Temp)

LOAD CustID,

  Month,

  IF(RecNo()=1 OR CustID <> Previous(CustID) OR BuyingCustFlag = 1 ,1,Peek(BuyMonthCnt)+1) as BuyMonthCnt

RESIDENT Temp

ORDER BY CustID,Month;

Final:

LOAD CustID,

  Month,

  BuyingCustFlag,

  BuyMonthCnt

RESIDENT Temp

WHERE Bought <> 'N';

DROP TABLE Temp;