topic Re: New Vs Repeat Customers in QlikView

New Vs Repeat Customers

atulkrsingh — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: New Vs Repeat Customers

Taoufiq_Zarra — Fri, 05 Jun 2020 12:09:46 GMT

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

Re: New Vs Repeat Customers

atulkrsingh — Fri, 05 Jun 2020 13:42:19 GMT

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

Re: New Vs Repeat Customers

Saravanan_Desingh — Fri, 05 Jun 2020 13:33:41 GMT

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

Re: New Vs Repeat Customers

atulkrsingh — Fri, 05 Jun 2020 13:43:21 GMT

Hi Saran,

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

Re: New Vs Repeat Customers

Taoufiq_Zarra — Fri, 05 Jun 2020 14:13:10 GMT

@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

Re: New Vs Repeat Customers

atulkrsingh — Fri, 05 Jun 2020 14:25:24 GMT

Thanks a lot, Taoufiq!