Calculate new and lost customers

[#1]

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

[jagan]

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,

[jagan · Accepted solution]

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,

[#4]

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!