topic Calculate new and lost customers in QlikView

Calculate new and lost customers

Fri, 26 Feb 2010 18:07:53 GMT

Hello everybody!

Is there an elegant way to state how many customers we have gained in a given year compared to the previour year? In the attached example Tom is a loyal customer, Jim is a new one and Sam is a lost one. If I create a table years/customer type I therefore need to get 1 new customer and 1 lost customer for 2010.

Any suggestion please?

I apologise in advance since I will be able to reply only on Monday morning, but I believe in time zones benefits!

Thanks,
Luca

Calculate new and lost customers

jagan — Fri, 26 Feb 2010 18:24:22 GMT

hi,

If you add another field to the table specifying whether the customer is new or left, then we can calculate the number of new and left customers easily.

LOAD * INLINE [
Customer, Year, Type
Tom, 2009, New
Tom, 2010, Exit
Jim, 2010, New
Sam, 2009, New
];

Then use the expressions

Count({$<Type={'New'}>}Customer) //New Customers count

Count({$<Type={'Exit'}>}Customer) //left customers count

Hope this helps you

Regards,

Calculate new and lost customers

jagan — Fri, 26 Feb 2010 18:38:50 GMT

hi,

This script would eliminate to add the new column

TempCustomers:

LOAD * INLINE [
Customer, Year
Tom, 2009
Tom, 2010
Jim, 2010
Sam, 2009];

Customers:
Load
Customer,
Year,
Prev_customer,
if(Prev_customer=Customer, 'Exit', 'New') AS Status;
LOAD
Customer,
Year,
Previous(Customer) AS Prev_customer
Resident
TempCustomers
order by Customer, Year;

Drop table TempCustomers;

Regards,

Calculate new and lost customers

Mon, 01 Mar 2010 16:56:53 GMT

Thank you Sachin! I'll work on your suggestion. The set analysis filter is also something really helpful that I've been looking for, but I always stucked with the syntax.

Cheers!