Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master II
Master II

@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
Master II
Master II

@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") 😉
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

 

 

 

 

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

Master II
Master II

@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

Contributor II
Contributor II

Thanks a lot, Taoufiq!