Question on repeat customers for products

[#1]

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	5	4	80%
BOX	5	5	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;

[MarcoWedel]

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

[#3]

Marco,

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

[MarcoWedel]

You're welcome

regards

Marco