# 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

## New Vs Repeat Customers

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

Sample Data:

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

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 :

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") 😉
6 Replies
Highlighted
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

Hi Taoufiq,

Thanks for responding.

The expected output is:

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

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

Highlighted
Contributor II

Hi Saran,

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

Highlighted
Master

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 :

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") 😉
Contributor II

Thanks a lot, Taoufiq!