topic Re: Customers retention in QlikView

Customers retention

Sun, 22 Jan 2017 21:29:41 GMT

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

Re: Customers retention

sunny_talwar — Mon, 23 Jan 2017 02:22:23 GMT

May be something like this:

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

Re: Customers retention

Mon, 23 Jan 2017 08:44:15 GMT

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)

Re: Customers retention

sunny_talwar — Mon, 23 Jan 2017 10:45:16 GMT

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?

Re: Customers retention

Mon, 23 Jan 2017 11:16:15 GMT

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

Re: Customers retention

Mon, 23 Jan 2017 11:49:31 GMT

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

Re: Customers retention

sunny_talwar — Mon, 23 Jan 2017 12:10:32 GMT

May be this?

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

Re: Customers retention

Mon, 23 Jan 2017 18:12:40 GMT

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

Re: Customers retention

sunny_talwar — Mon, 23 Jan 2017 18:18:45 GMT

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))

Re: Customers retention

Mon, 23 Jan 2017 18:45:54 GMT

Thank you very much, it works