Lost, New and Loyal Customers month wise

[renjithpl]

Hi All,

I am trying to find Lost, New and Loyal customers month wise.

User will select year and a month, a pivot table should show the count of the customers lost in current month, new customers in current month and loyal customers.

Also when user search for a customer, it should fall on these three categories.

I have searched in the community, but could not find the correct solution.

regards

[whiteline]

What is "Lost, New and Loyal", Attributes, Fields, Group ?

To answer your question the way the info is stored is important.

[renjithpl · Accepted solution]

Whiteline -

Lost Customers - who made sales previous month but not current month

New Customers - who made sales current month but not previous month

Loyal Customers - who made sales previous month and current month

I tried something like below - It works,

for loyal - count({<Year=, Month=, FY=, CustomerCode= P({<BilledYearMonth={"$(=monthname(max(BilledYearMonth)-1))"}>}), BilledYearMonth={"$(=monthname(max(BilledYearMonth)))"} >} distinct CustomerCode)

for New - count({<Year=, Month=, FY=, CustomerCode= E({<BilledYearMonth={"$(=monthname(max(BilledYearMonth)-1))"}>}), BilledYearMonth={"$(=monthname(max(BilledYearMonth)))"} >}distinct CustomerCode)

for Lost -

count({<Year=, Month=, FY=,  CustomerCode= E({<BilledYearMonth={"$(=monthname(max(BilledYearMonth)))"}>}),

  BilledYearMonth={"$(=monthname(max(BilledYearMonth)-1))"} >} DISTINCT CustomerCode)

Cheers !!

[#4]

thanx.

It realy helpfull.

[#5]

Hi Renjith

I adapted your solution for my own analysis and it really works!

However, I do not quite understand the code. Could you explain what does it do?

Thanks!

Dingyong

[cardonne_esrf]

Hi,

What forms does "BilledYearMonth" have?

Chers.