# New to QlikView

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

Announcements
cancel
Showing results for
Did you mean:
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.

1 Solution

Accepted Solutions
Master II

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
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

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

Specialist III

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

Contributor II

Hi Saran,

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

Master II

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!