topic Re: Question on repeat customers for products in QlikView

Question on repeat customers for products

Wed, 27 Aug 2014 20:13:38 GMT

I am trying to get a chart looking like the one below.

PRODUCT

SUM(Orders)

SUM(Repeat Customers Orders)

% of the orders are repeat customers

BALL

80%

BOX

100%

I added a count and IsInYTD for more help:

InputTable:

LOAD * INLINE [

Product,CustomerNO,OrderDate

ball,1,'2013-01-14 00:00:00.000'

ball,2,'2013-05-07 00:00:00.000'

ball,1,'2013-07-24 00:00:00.000'

ball,5,'2014-07-20 00:00:00.000'

ball,5,'2014-07-24 00:00:00.000'

box,5,'2014-02-05 00:00:00.000'

box,3,'2013-05-07 00:00:00.000'

box,5,'2014-02-05 00:00:00.000'

box,3,'2014-07-24 00:00:00.000'

box,5,'2013-01-14 00:00:00.000'

];

InputTable2:

Load

Product AS InputTable2_Product

,CustomerNO AS InputTable2_CustomerNO

,OrderDate AS InputTable2_OrderDate

,If( DayNumberOfYear(OrderDate) <= DayNumberOfYear(Today()), 1, 0 ) AS InputTable2_IsInYTD

,1 AS InputTable2_Counter

Resident InputTable;

Re: Question on repeat customers for products

MarcoWedel — Wed, 27 Aug 2014 23:39:08 GMT

Hi,

one possible solution:

InputTable:

LOAD * INLINE [

Product, CustomerNO, OrderDate

ball, 1, 2013-01-14 00:00:00.000

ball, 2, 2013-05-07 00:00:00.000

ball, 1, 2013-07-24 00:00:00.000

ball, 5, 2014-07-20 00:00:00.000

ball, 5, 2014-07-24 00:00:00.000

box, 5, 2014-02-05 00:00:00.000

box, 3, 2013-05-07 00:00:00.000

box, 5, 2014-02-05 00:00:00.000

box, 3, 2014-07-24 00:00:00.000

box, 5, 2013-01-14 00:00:00.000

];

Left Join (InputTable)

LOAD CustomerNO,

If(Count(CustomerNO)>1, 'Y', 'N') as [Repeat Customer]

Resident InputTable

Group By CustomerNO;

hope this helps

regards

Marco

Re: Question on repeat customers for products

Thu, 28 Aug 2014 15:06:46 GMT

Marco,

Thank you very much for the help. I think this will get me in the right direction.

Re: Question on repeat customers for products

MarcoWedel — Thu, 28 Aug 2014 15:26:45 GMT

You're welcome

regards

Marco