New/Lost/Returning/Loyal Customers

[hvfalcao]

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)

[Gysbert_Wassenaar]

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.

[hvfalcao]

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!

[Gysbert_Wassenaar]

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.

[hvfalcao]

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!

[Gysbert_Wassenaar]

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

[hvfalcao]

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!

[Gysbert_Wassenaar]

Try this one then.

[gardenierbi · Accepted solution]

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;

[Gysbert_Wassenaar]

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.