Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

New/Lost/Returning/Loyal Customers

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)

1 Solution

Accepted Solutions
Highlighted
Contributor

Re: New/Lost/Returning/Loyal Customers

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;

View solution in original post

12 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: New/Lost/Returning/Loyal Customers

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.


talk is cheap, supply exceeds demand
Highlighted
Contributor

Re: New/Lost/Returning/Loyal Customers

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!

MVP & Luminary
MVP & Luminary

Re: New/Lost/Returning/Loyal Customers

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.


talk is cheap, supply exceeds demand
Highlighted
Contributor

Re: New/Lost/Returning/Loyal Customers

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) ... Capturar1.JPG

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: New/Lost/Returning/Loyal Customers

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


talk is cheap, supply exceeds demand
Highlighted
Contributor

Re: New/Lost/Returning/Loyal Customers

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!

Highlighted
MVP & Luminary
MVP & Luminary

Re: New/Lost/Returning/Loyal Customers

Try this one then.


talk is cheap, supply exceeds demand
Highlighted
Contributor

Re: New/Lost/Returning/Loyal Customers

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;

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: New/Lost/Returning/Loyal Customers

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.


talk is cheap, supply exceeds demand