New Vs Repeat Customers

[atulkrsingh]

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!

[Taoufiq_Zarra]

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

[atulkrsingh]

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

[Saravanan_Desingh]

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

[atulkrsingh]

Hi Saran,

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

[Taoufiq_Zarra · Accepted solution]

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

attached qvw file

[atulkrsingh]

Thanks a lot, Taoufiq!