topic Re: counting returning customers in QlikView

counting returning customers

nicolai_moller — Thu, 26 Mar 2015 09:15:59 GMT

I have a list of customers and each customers orderDates. I'm trying to count the number of customers who have made orders the following years.

Of the 4 customers who have made orders in 2012, 2 of 4 have also ordered in 2013 and 1 of 4 have one in 2014.

How can this be made?

Thanks

jpenuliar — Thu, 26 Mar 2015 09:37:34 GMT

You can add a Year field to your table and present in a pivot table

anbu1984 — Thu, 26 Mar 2015 09:48:38 GMT

Load *,Year(OrderDate) As Yr;

Load CustomerID,Date#(OrderDate,'MM-DD-YYYY') As OrderDate Inline [

CustomerID,OrderDate

1,12-12-2012

2,01-01-2012

2,05-05-2013

3,07-07-2014

4,06-06-2015

5,04-04-2012

6,08-08-2013

7,01-01-2012

7,01-01-2013

7,01-01-2014 ];

='Returing customers in 2013: ' &Count(DISTINCT {<CustomerID=P({<Yr={'<2013'}>})>*<CustomerID=P({<Yr={2013}>})>}CustomerID)

='Returing customers in 2014: ' &Count(DISTINCT {<CustomerID=P({<Yr={'<2013'}>})>*<CustomerID=P({<Yr={2014}>})>}CustomerID)

sudeepkm — Thu, 26 Mar 2015 09:53:44 GMT

As Jonathan suggested you can use Year as dimension in a pivot table like below.

here you can find number of orders by year and again you can go to the second dimension to find who all have ordered in a particular year.

Ralf-Narfeldt — Thu, 26 Mar 2015 11:45:13 GMT

If you want to do it in the script, you can add this resident load after you load the Orders table (you may have named it differently).

It will create a linked table with for each customerID:

ReturnedTimeSpan: diff between first and last order year

ReturnedYears: number of distinct years the customer has ordered

I'm sure you can do more according to what you really want with this as starting point.

Returning:

Load

CustomerID,

Max(Year(OrderDate)) - Min(Year(OrderDate)) As ReturnedTimeSpan,

Count(DISTINCT Year(OrderDate)) As ReturnedYears

RESIDENT Orders GROUP BY CustomerID;