Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Rolling 3 month customers | |||||||||
Current Data | |||||||||
Date | CustomerID | ||||||||
12/15/2015 | 1 | ||||||||
12/13/2015 | 2 | ||||||||
12/1/2015 | 2 | ||||||||
12/12/2015 | 4 | ||||||||
1/11/2015 | 1 | ||||||||
1/15/2015 | 3 | ||||||||
1/1/2015 | 4 | ||||||||
2/14/2015 | 5 | ||||||||
2/17/2015 | 3 | ||||||||
2/16/2015 | 1 | ||||||||
2/1/2015 | 2 | ||||||||
3/17/2015 | 5 | ||||||||
3/21/2015 | 1 | ||||||||
3/14/2015 | 3 | ||||||||
3/11/2015 | 4 | ||||||||
WANT | |||||||||
Feb-15 | 1 | -- CustomerID 1 made a transaction on Feb, Jan and Dec | |||||||
Mar-15 | 2 | -- Cust ID 1 and 3 made a transaction on March, Feb and Jan | |||||||
Basically want the distinct count of repeat customers on the last 3 months
Try this script:
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/11/2015, 4
];
FinalTable:
LOAD *,
If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and
CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;
LOAD DISTINCT
MonthYear,
CustomerID
Resident Table
Order By CustomerID, MonthYear;
On the front end create a straight table with MonthYear as dimension and Sum(Flag) as Expression
Do you need a front end solution or can a little back end scripting to create flags can be done here?
I am ok with either solution. Front end or back end, whichever gets me the correct solution. Thanks!
Try this script:
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/11/2015, 4
];
FinalTable:
LOAD *,
If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and
CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;
LOAD DISTINCT
MonthYear,
CustomerID
Resident Table
Order By CustomerID, MonthYear;
On the front end create a straight table with MonthYear as dimension and Sum(Flag) as Expression
Thanks Sunny, Appreciate the help,
I have a similar problem, would you be able to help me alter the answer to only count last 3 months not including this month. For example:
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
11/5/2014,3
11/25/2014,2
11/7/2014,1
11/30/2014,4
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/11/2015, 4
4/5/2015,2
4/15/2015,5
4/1/2015,1
4/28/2015,3
];
WANT:
April 2015 - 2
March 2015- 1
Feb 2015- 2
Jan 2015 - 0
Dec 2015 - 0
Nov 2015 - 0
I have created a new thread for it, I have attached the post to you.
Distinct Customers last 3 months rolling
Thanks for the help!