How to Get New and Lost Customers

[#1]

Hi,

I would like to get the list of new, kept and lost customers in different text boxes.

Data set:

Sales:

LOAD * INLINE [

    Year, Customer, Sales

    2011, A, 100

    2011, B, 88

    2012, A, 121

    2012, C, 90

    2011, D, 99

    2012, D, 0

];

Results:

• C is a new customer. He didn't have any sales in 2011, but had some in 2012.
• B and D were lost customers. They had sales in 2011, but nothing in 2012.
• Finally, A is a kept customers. Sales are available for both years.

Any idea on how I could achieve this?

Example attached.

Thanks for any input !

Robert

[vincent_ardiet · Accepted solution]

Hi Robert,

Try this :

='New : '&

concat(distinct {$<Customer=p({$<Year={2012},Sales={'>0'}>} Customer)>-<Customer=p({$<Year={2011},Sales={'>0'}>} Customer)>} Customer

,';')

='Lost : '&

concat(distinct {$<Customer=p({$<Year={2011},Sales={'>0'}>} Customer)>-<Customer=p({$<Year={2012},Sales={'>0'}>} Customer)>} Customer

,';')

='Kept : '&

concat(distinct {$<Customer=p({$<Year={2011},Sales={'>0'}>} Customer)>*<Customer=p({$<Year={2012},Sales={'>0'}>} Customer)>} Customer

,';')

Regards,

Vincent

[#3]

Perfect

[vincent_ardiet]

In fact you can make a bit shorter and combine with E() (just for fun ) :

='New : '&

concat(distinct {$<Customer=p({$<Year={2012},Sales={'>0'}>} Customer)*e({$<Year={2011},Sales={'>0'}>} Customer)>} Customer

,';')

='Lost : '&

concat(distinct {$<Customer=p({$<Year={2011},Sales={'>0'}>} Customer)*e({$<Year={2012},Sales={'>0'}>} Customer)>} Customer

,';')

='Kept : '&

concat(distinct {$<Customer=p({$<Year={2011},Sales={'>0'}>} Customer)*p({$<Year={2012},Sales={'>0'}>} Customer)>} Customer

,';')

Regards,

Vincent