5 Replies Latest reply on May 16, 2018 3:02 PM by Carlos Arias Fernandez

# Sales/Profits of lost customers in previous years

Hi,

Goal

I am trying to analyze several metrics (such as sales, profit margin, location etc) of lost customers when they were still customers.

Lost customer is defined as :

{FIXED [Customer], [Year] : SUM([Sales]) } >0 AND {FIXED [Customer], [Year]+1 : SUM([Sales]) } <=0

I believe the syntax above is not right but I am using it conceptually

The idea is to be able to analyze what were the sales, profits or locations of those customer before they were lost. Is this possible?

Data explanation

The data has a row for every item purchased by a customer.

Each customer can purchase multiple items within a year

In the original data there are more than 50K items and thousands of customer and there si no predictable order in the rows

Expected Results

Example 1: A graph/table that shows that Customer B was lost in 2014 and its sales in 2013 were \$17

Example 2: A graph/table that shows that Customer A and D were lost in 2016 and their profit margin in 2015 were (7+1)/(10+5) for customer A and (7+2)/(10+7) for customer B

I would appreciate any help guidance

Thanks

• ###### 1. Re: Sales/Profits of lost customers in previous years

Hi, Carlos

Is it something like this? If yes, please find my solution attached.

Hope this helps and let me know if you have question

ZZ

• ###### 2. Re: Sales/Profits of lost customers in previous years

Hello Carlos,

Clear and logical.

My solution is similar but with more user interaction, by using filter and parameter.

Please refer to the attached workbook for details.

Regards

Lei

1 of 1 people found this helpful
• ###### 3. Re: Sales/Profits of lost customers in previous years

This is very helpful and exactly what I was looking for in my question. Thanks a lot.

But Customer B was lost during 2014 and 2015. How could I show that in 2014 Customer B was lost and its Sales, profits, etc the years before?

What if some of the sales entries are zero? I modified the data file to show that customer A has an entry in 2015 but the sum of the items is is zero. Therefore the last year Customer A was a customer is not 2015 but 2014.

My goal is to be able to see for each year:

- How many customers we lost

- What were their profit margin, Sales, Profit the year before

- Where they were located

Thanks

• ###### 4. Re: Sales/Profits of lost customers in previous years

Hello Carlos,

Very interesting scenarios!

Let me call them,

Case 1

Carlos Arias Fernandez wrote:

But Customer B was lost during 2014 and 2015. How could I show that in 2014 Customer B was lost and its Sales, profits, etc the years before?

Case 2

Carlos Arias Fernandez wrote:

What if some of the sales entries are zero? I modified the data file to show that customer A has an entry in 2015 but the sum of the items is is zero. Therefore the last year Customer A was a customer is not 2015 but 2014.

Case 2 is simple, update the calculation and this can easily be achieved.

See worksheet "Example".

Case 1 is tough to obtain in the style of worksheet "Example".

Carlos Arias Fernandez wrote:

My goal is to be able to see for each year:

- How many customers we lost

- What were their profit margin, Sales, Profit the year before

- Where they were located

Dashboard "Fact Check"

It is the list of customers and their status.

If there's no CHECK mark, the customer is lost in this year.

Click the CHECK mark, the sales details of this customer will be shown.

(click CHECK mark)

Dashboard "Summary Check"

It is the summary of each status (during the selected year and the previous year).

Select the year, and see the customer count of each status.

Click a status "Lost", and see who is lost in details.

(click "Lost")

Please check out the attached workbook for details.

Regards

Lei

• ###### 5. Re: Sales/Profits of lost customers in previous years

Ok, you are a genius. I would have never been able to do this by myself.

I adapted the solution to my data and it works perfectly.

Thank you so much !!! You are amazing

2 of 2 people found this helpful