Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 can you share the expected output from this sample ?
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
So, for 2018 the 'Repeat Customer' is not 1? How did you get 2 here?
Hi Saran,
Thanks a lot for pointing that out. I have corrected it. It will be 1
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
Thanks a lot, Taoufiq!