Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct count of customers in last 3 months

Rolling 3 month customers
Current Data
DateCustomerID
12/15/20151
12/13/20152
12/1/20152
12/12/20154
1/11/20151
1/15/20153
1/1/20154
2/14/20155
2/17/20153
2/16/20151
2/1/20152
3/17/20155
3/21/20151
3/14/20153
3/11/20154
WANT
Feb-151-- CustomerID 1 made a transaction on Feb, Jan and Dec
Mar-152-- 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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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

Not applicable
Author

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

sunny_talwar

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

Capture.PNG

Not applicable
Author

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!