topic Re: New/Lost/Returning/Loyal Customers in QlikView

New/Lost/Returning/Loyal Customers

hvfalcao — Fri, 18 Sep 2015 13:07:39 GMT

Hi!

I'm struggling to find a solution to count new/lost/returning/loyal customer over the years... I've tried different approaches posted on the community (script and expression), but couldn't reach what I want. I need it on expression! I'll neded to select years on a filter, use it on graphs...

Definitions:

New - clients that never bought, and bought on the selected year.
Lost - clients that have bought last year, but not the selected year.

Returning - clients that have bought in any year before, didn't bought the previous year, but bought the selected year.

Loyal - clients that bought previous year, and the selected year.

The previous year is always compared to the selected year, not the actual year, like today...

The data (simplified):

ORDERID

CLIENTID

YEAR

SALESQTY

IDPRODUCTCATEGORY

Thank you!

(I cant open others apps)

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Fri, 18 Sep 2015 17:19:58 GMT

Try out some of the previous solutions to this question: https://community.qlik.com/search.jspa?q=new+lost+customers‌

If you need more help then post a small qlikview document with some realistic example data.

Re: New/Lost/Returning/Loyal Customers

hvfalcao — Fri, 18 Sep 2015 18:10:52 GMT

Hi Gysbert,

As I said above, I've already tried almost all solutions posted on the community...

I'm creating this app on Sense. I've posted on view, because there's more member to help.

Follow the attachment... Its a table with Order Year and Count of Total Customers...

Thank you!

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Sat, 19 Sep 2015 07:45:12 GMT

Try these:

New: count({<CLIENTID=E({<YYEAR={'<$(=max(YYEAR))'}>}CLIENTID)>} distinct CLIENTID)

Lost: count({<YYEAR={'$(=max(YYEAR)-1)'},CLIENTID=E({<YYEAR={'$(=max(YYEAR))'}>}CLIENTID)>} distinct CLIENTID)

Returning: count({<CLIENTID=P({<YYEAR={'<$(=max(YYEAR)-1)'}>}CLIENTID)>*<CLIENTID=E({<YYEAR={'$(=max(YYEAR)-1)'}>}CLIENTID)>} distinct CLIENTID)

Loyal: count({<YYEAR={'$(=max(YYEAR)-1)'},CLIENTID=P({<YYEAR={'$(=max(YYEAR))'}>}CLIENTID)>} distinct CLIENTID)

To be honest, this isn't really different from a lot of existing solutions. The field names are different, but the principle is the same. Use the P() and E() functions to select customers that do or don't have records in a certain period.

Anyway, I hope these work for you. You can use them in Qlik Sense as well as Qlikview.

Re: New/Lost/Returning/Loyal Customers

hvfalcao — Mon, 21 Sep 2015 14:39:55 GMT

Hi Gysbert,

I've already tried that. It works if I select (filter) just one year, but if I select all years, or more than one, or use it on graphs, it won't work, because it'll show just the MAX(YYEAR) ...

I've appreciated your help anyway... Thank you!

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Fri, 25 Sep 2015 16:55:06 GMT

I've tried to come up with a solution for that. I managed to create something that seems to be working.

Re: New/Lost/Returning/Loyal Customers

hvfalcao — Fri, 25 Sep 2015 17:14:45 GMT

Gysbert,

I can't open .qvw from others... As I'm developing on Sense, I just have the free edition of QV...

Do you mind to post like your first answer? Thank you again!

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Fri, 25 Sep 2015 17:27:26 GMT

Try this one then.

Re: New/Lost/Returning/Loyal Customers

gardenierbi — Sat, 26 Sep 2015 09:47:50 GMT

gwassenaar‌ a nice solution but :

Your code takes all the ClientId's and not only the ClientId's with sales
It's a little bit difficult to read/understand your code

Hereby the same solution but then with a less technical script. Your new table contains almost 48.000 rows, my solution only 1.318 rows. The outcome is identical but then easier with a better performance!

YearsPerClient:

LOAD DISTINCT

CLIENTID,

FirstYearOfSales + ITERNO() - 1 AS SalesYear

WHILE FirstYearOfSales + ITERNO() - 1 <= IF(LastYearOfSales < YEAR(TODAY()), LastYearOfSales + 1, YEAR(TODAY()));

// LastYearOfSales + 1 to count the lost clients!

LOAD

CLIENTID,

MIN(YYEAR) AS FirstYearOfSales,

MAX(YYEAR) AS LastYearOfSales

RESIDENT TABORDER

GROUP BY

CLIENTID;

LEFT JOIN (YearsPerClient)

LOAD DISTINCT

CLIENTID,

YYEAR AS SalesYear,

1 AS SalesInYear

RESIDENT TABORDER;

ReturningNew:

LOAD

CLIENTID,

SalesYear,

SalesInYear,

IF(CLIENTID = PREVIOUS(CLIENTID), NULL(), SalesInYear) AS isNewCustomer,

IF(CLIENTID = PREVIOUS(CLIENTID), IF(ALT(SalesInYear, 0) = 1 AND ALT(PREVIOUS(SalesInYear), 0) = 1, 1, NULL()), NULL()) AS isKeptCustomer,

IF(CLIENTID = PREVIOUS(CLIENTID), IF(ALT(SalesInYear, 0) = 1 AND ALT(PREVIOUS(SalesInYear), 0) <> 1, 1, NULL()), NULL()) AS isReturningCustomer,

IF(CLIENTID = PREVIOUS(CLIENTID), IF(ALT(SalesInYear, 0) <> 1 AND ALT(PREVIOUS(SalesInYear), 0) = 1, 1, NULL()), NULL()) AS isLostCustomer

RESIDENT YearsPerClient

ORDER BY

CLIENTID,

SalesYear;

DROP TABLE YearsPerClient;

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Sat, 26 Sep 2015 14:46:49 GMT

Yeah, that's certainly an improvement. I was going to revise my solution since I wasn't satisfied with it yet and try to clean things up a but, but you beat me to it with a very nice solution.

Edit: I've cleaned things up a bit anyway. Cleaning out the unnecessary records is done easily enough, though I don't think it's really necessary. 48 thousand records is trivial for such a table and shouldn't cause any noticeable performance degradation.

Re: New/Lost/Returning/Loyal Customers

hvfalcao — Mon, 28 Sep 2015 14:57:10 GMT

Very nice and clean solution! That was exactly what I need!

Thank you!

Re: New/Lost/Returning/Loyal Customers

hectorgarcia — Thu, 08 Oct 2015 20:41:07 GMT

This is good stuf, if you want to measure the retirning customers by Monthyear instead of year you will just need to change thus customer history tab logic to Monthname?

Thanks

Re: New/Lost/Returning/Loyal Customers

Gysbert_Wassenaar — Fri, 09 Oct 2015 09:36:38 GMT

Basically yes. But MonthName values are dates, not sequential numbers like years. So you need to use the addmonths function instead of just adding integers.

Re: New/Lost/Returning/Loyal Customers

fluxfrog — Mon, 05 Oct 2020 10:03:22 GMT

Thanks for this solution to a quite tricky problem! Worked great for my app too.