Current vs. previous year

[#1]

I have the following data.

Year	Customer	Amount
2010	ABC	200000
2011	ABC	240000
2012	ABC	288000
2013	ABC	345600
2014	ABC	414720
2015	ABC	497664
2012	BCD	125000
2013	BCD	250000

I want the following:

a. loyal customers. i.e. the list of customers who purchase regularly in all the years

b. new customers -  when I select a year, I want the list of new customers added in that year and their amounts

c. Lost customers - Customers who didn't business forever. for eg, BCD didn't do business for 2014 and 2015.

[marcus_sommer]

Have a look on the last links in this posting: How to use - Master-Calendar and Date-Values.

- Marcus

[jagan]

Hi,

Try like this

a. loyal customers -

Dimension: Customer

Expression: Sum(Aggr(If(Count(Distinct Year) = Count({1} Year), 1, 0), Customer))

b. new customers

Dimension: Customer

Expression: Sum({<Customer=E({1-$} Customer)>} Amount)

c. Lost customers

Dimension: Customer

Expression: Aggr(If(Sum({<Year={'$(=Max(Year))', '$(=Max(Year) - 1)'}>} Amount) = 0, 'No Sales in Last two Years'), Customer)

Hope this helps you.

Regards,

Jagan.

[#4]

Jagan,

Thanks a lot.

It works fine.

but it compares only the previous year.

for eg I have  saes in 2011 to customer A and in 2014

Then it is classified as new customer in 2014. It compares with 2013 not for all the prior years.

Can you please help me in this.

[jagan]

PFA

for solution.

a. loyal customers -

Dimension: Customer

Expression: Sum(Aggr(If(Count(Distinct Year) = Count(DISTINCT TOTAL Year), 1, 0), Customer))

b. new customers

Dimension: Customer

Expression: Sum(Aggr(If(Count(DISTINCT Year) = 1 AND Count(TOTAL DISTINCT Year) = 1, Sum(Amount)), Customer))

c. Lost customers

Dimension: Customer

Expression: If(Len(Only({<Customer=E({1<Year={'$(=Max(Year))', '$(=Max(Year) - 1)'}>} Customer)>} Customer)) > 0, 1)

Hope this helps you.

Regards,

Jagan.

[#6]

sorry to trouble you again.

in the attachment, if there is sales in 2011 and 2013 but not in 2012, then it is considered as new customer in 2011 and 2013.

if I select 2014, it should show BCD as it is a lost customer, not only in 2014 but also in 2015.

Can you please help.

[jagan]

Try like this

New Customer: If(Sum({<Year={'$(=Max(Year))'}>} Amount) > 0 AND Sum({<Year={'$(=Max(Year)-1)'}>} Amount) = 0, Sum(Amount))

Lost Customer: If(Sum(Amount) =0 AND Sum({<Year=E(Year)>} Amount) > 0, 1)

Regards,

Jagan.

[sinanozdemir]

Hi Kartik,

I am not sure about your business requirements, but here is my approach (I would like to handle this in the load script as much as possible):

In the front end, you can create a straight table and use the two new dimensions:

Here is the expression for Active/Lost Customer:

Hope this helps.

Thanks

[jagan]

Hi Kartik,

If you got the answer close this thread by giving Correct and useful answers.

Regards,

Jagan.

[qlikviewwizard]

Hi Kartik,

If you got the answer close this thread by giving Correct and useful answers.