topic Re: How to Get New and Lost Customers in QlikView

How to Get New and Lost Customers

Tue, 25 Sep 2012 13:53:42 GMT

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

Re: How to Get New and Lost Customers

vincent_ardiet — Tue, 25 Sep 2012 14:13:59 GMT

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

Re: How to Get New and Lost Customers

Tue, 25 Sep 2012 14:21:10 GMT

Perfect

Re: How to Get New and Lost Customers

vincent_ardiet — Tue, 25 Sep 2012 14:35:54 GMT

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