Distinct count of customers in last 3 months

[#1]

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

[sunny_talwar]

Do you need a front end solution or can a little back end scripting to create flags can be done here?

[#3]

‌I am ok with either solution. Front end or back end, whichever gets me the correct solution. Thanks!

[sunny_talwar · Accepted solution]

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

[#5]

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!