Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Have a look on the last links in this posting: How to use - Master-Calendar and Date-Values.
- Marcus
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.
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.
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.
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.
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.
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
Hi Kartik,
If you got the answer close this thread by giving Correct and useful answers.
Regards,
Jagan.
Hi Kartik,
If you got the answer close this thread by giving Correct and useful answers.