Customers retention

[#1]

Hello,

I'm trying to list all customers in current year who had vNetSales >5000 and had vNetSales <=6000 in previous year.

it looks simple but I couldn't find the exact expression yet.

I reached to the below but couldn't figure out how to put the conditions of <>amount

count(
{<
Year={$(=Max(Year))},
customer=E({<Year={$(=Max(Year)-1)}>}customer)
>}
customer)

I appreciate any help. thank you in advance,

Rami

[sunny_talwar]

May be something like this:

Count(DISTINCT {<Customer = {"=Sum({<Year = {$(=Max(Year))}>}Sales) > 5000 and Sum({<Year = {$(=Max(Year) -1)}>}Sales) <= 6000"}>} Customer)

[#3]

Thank you for your answer. I have simplified your expression a bit to become the below but it didnt work. Also you didnt take into consideration that I want to eliminate the customers of previous year meeting the condition and not count them. this can be accomplished by e() function.

the solution is mix of your above expression and e() function but I cannot get it right yet.

=Count(DISTINCT

{<

customer =

{"

=Sum({<Year = {2016}>} invvalue) > 100

and Sum({<Year = {2015}>} invvalue) <= 700

"}

>}

customer)

[sunny_talwar]

Would you be able to share a sample where you might be trying out and also point out what the required output needs to be?

[#5]

Thank you Sunny for followup, please see below the example

raw data

sales_table:

LOAD * INLINE [

    Year, customer, invvalue,invno, note

    2015, x, 10,1,low sales in previous year, should be considered new customer next year

    2015, x, 20,2, low sales in previous year, should be considered new customer next year

    2015, y, 300,1, old customer

    2015, y, 600,2, old customer

    2016, y, 700,1, old customer

    2016, y, 800,2, old customer

    2016, x, 600,1, new customer since last year sales was very low

    2016, x, 50,2, new customer since last year sales was very low

    2016, a, 400,1, new customer

    2016, b, 200,1, new customer

];

totals

2016	y	150
2015	y	500
2015	x	30
2016	x	150
2016	b	600
2016	a	400

customer X should be considered as new customer in 2016 since his sales was low in 2015

customer B should not be counted as new customer since his sales is less than 500

current expression

=COUNT(

{<

customer ={"= Sum ( {<Year={2016},

customer=E({<Year={2015} >} customer)

>}invvalue)> 500  " }

>}

DISTINCT customer)

Problem

unable to avoid filtering customers from previous year if they have low sales

Thanking you in advance

[#6]

I reached to solution...

=COUNT(

{<

customer -={"= Sum ( {<Year={2015}

>}invvalue)> 500  " }

,

Year={2016}

>}

DISTINCT customer)

but now i want to filter out customers of 2016 with total sales < 1000, I cannot get the syntax right for creating a formula in set analysis

[sunny_talwar]

May be this?

=Count({<customer = {"=Sum({<Year={2016}>}invvalue) < 1000"}-{"=Sum({<Year={2015}>}invvalue) > 500"}, Year={2016}>} DISTINCT customer)

[#8]

Great, it works...

but I need to work on net sales (sales minus return) so my expression became very long and I'm worried on performance.

my final expression is as below

=sum({<customer = {"=Sum({<Year={$(=Max(Year))}>}invvalue)-Sum({<Year={$(=Max(Year))}>}retvalue) > vLowSales"}-{"=Sum({<Year={$(=Max(Year)-1)}>}invvalue)-Sum({<Year={$(=Max(Year)-1)}>}retvalue)> $(vPYLowSales)"}, Year={$(=Max(Year))} >}  invvalue)

-sum({<customer = {"=Sum({<Year={$(=Max(Year))}>}invvalue)-Sum({<Year={$(=Max(Year))}>}retvalue) > vLowSales"}-{"=Sum({<Year={$(=Max(Year)-1)}>}invvalue)-Sum({<Year={$(=Max(Year)-1)}>}retvalue)> $(vPYLowSales)"}, Year={$(=Max(Year))}>}  retvalue)

any advice on simplifying it with a variable or something

thanks

[sunny_talwar · Accepted solution]

May be this:

=Sum({<customer = {"=Sum({<Year={$(=Max(Year))}>} RangeSum(invvalue, -retvalue)) > vLowSales"}-{"=Sum({<Year={$(=Max(Year)-1)}>} RangeSum(invvalue, -retvalue)) > $(vPYLowSales)"}, Year={$(=Max(Year))}>} RangeSum(invvalue, -retvalue))

[#10]

Thank you very much, it works