Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

New Vs Repeat Customers

I'm new to Qlik and I'm trying to analyse new vs repeat customers.

Sample Data:

atulkrsingh_0-1591357249896.png

The logic is that,

I need to capture the customer as a new customer only when the first incidence of purchase occur 

and, I need to capture repeat customers only once in a year, irrespective of how many times they have purchased in that particular year. 

Thanks in Advance!

 

1 Solution

Accepted Solutions
Highlighted
Master
Master

@atulkrsingh 

one solution :

on laod Script :

 

Data:

load *,Year(Date#([Purchase Date],'DD-MMM-YY')) as Year inline [
Customer ID,Purchase Date
1,01-Jun-16
2,01-Aug-16
2,01-Nov-16
1,01-Feb-17
3,01-Apr-17
4,01-Aug-17
2,01-Mar-18
2,01-Dec-18
];

output:
noconcatenate

load *,if(Previous([Customer ID])=[Customer ID],'R','N') as Status;
load * resident Data order by [Customer ID],Year;

drop table Data;

 

 

and then create Table :

dimension -> Year

Measure 1 (New Customer) :

 

=count({<Status={'N'}>} distinct  [Customer ID])

 

Measure 2 (Repeat Customer)

 

=count({<Status={'R'}>} distinct [Customer ID])

 

 

result :

Capture.PNG

 

attached qvw file

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

6 Replies
Highlighted
Master
Master

@atulkrsingh  can you share the expected output from this sample ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor II
Contributor II

Hi Taoufiq,

Thanks for responding.

The expected output is:

atulkrsingh_0-1591364516973.png

 

Where,

in 2016, Customer ID = 1 and 2 are new customer whereas Customer ID 2 is a repeat customer

in 2017, Customer ID = 3 and 4 are new customer whereas Customer ID 1 is a repeat customer

and in 2018, Customer ID = 2 is a repeat customer

 

 

 

 

Highlighted
Specialist III
Specialist III

So, for 2018 the 'Repeat Customer' is not 1? How did you get 2 here?

Contributor II
Contributor II

Hi Saran,

Thanks a lot for pointing that out. I have corrected it. It will be 1

Highlighted
Master
Master

@atulkrsingh 

one solution :

on laod Script :

 

Data:

load *,Year(Date#([Purchase Date],'DD-MMM-YY')) as Year inline [
Customer ID,Purchase Date
1,01-Jun-16
2,01-Aug-16
2,01-Nov-16
1,01-Feb-17
3,01-Apr-17
4,01-Aug-17
2,01-Mar-18
2,01-Dec-18
];

output:
noconcatenate

load *,if(Previous([Customer ID])=[Customer ID],'R','N') as Status;
load * resident Data order by [Customer ID],Year;

drop table Data;

 

 

and then create Table :

dimension -> Year

Measure 1 (New Customer) :

 

=count({<Status={'N'}>} distinct  [Customer ID])

 

Measure 2 (Repeat Customer)

 

=count({<Status={'R'}>} distinct [Customer ID])

 

 

result :

Capture.PNG

 

attached qvw file

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks a lot, Taoufiq!