Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | ||
---|---|---|---|---|---|
| 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;
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
Marco,
Thank you very much for the help. I think this will get me in the right direction.
You're welcome
regards
Marco