topic Re: Lost, New and Loyal Customers month wise in QlikView

Lost, New and Loyal Customers month wise

renjithpl — Tue, 28 Aug 2012 04:43:48 GMT

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

Re: Lost, New and Loyal Customers month wise

whiteline — Tue, 28 Aug 2012 13:17:42 GMT

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

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

Re: Lost, New and Loyal Customers month wise

renjithpl — Wed, 29 Aug 2012 07:13:55 GMT

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 !!

Re: Lost, New and Loyal Customers month wise

Wed, 29 Aug 2012 12:48:43 GMT

thanx.

It realy helpfull.

Re: Lost, New and Loyal Customers month wise

Sun, 03 Jan 2016 15:54:13 GMT

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

Re: Lost, New and Loyal Customers month wise

cardonne_esrf — Mon, 10 Feb 2025 15:39:01 GMT

Hi,

What forms does "BilledYearMonth" have?

Chers.