9 Replies Latest reply: Aug 12, 2015 10:34 PM by arjun rao

# Current vs. previous year

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.

• ###### Re: Current vs. previous year

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

- Marcus

• ###### Re: Current vs. previous year

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.

• ###### Re: Current vs. previous year

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.

• ###### Re: Current vs. previous year

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.

• ###### Re: Current vs. previous year

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.

• ###### Re: Current vs. previous year

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.

• ###### Re: Current vs. previous year

Hi Kartik,

Regards,

Jagan.

• ###### Re: Current vs. previous year

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

• ###### Re: Current vs. previous year

Hi Kartik,