Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
hvfalcao
Creator
Creator

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
gardenierbi
Creator
Creator

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

13 Replies
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.


talk is cheap, supply exceeds demand
hvfalcao
Creator
Creator
Author

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.


talk is cheap, supply exceeds demand
hvfalcao
Creator
Creator
Author

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!

Gysbert_Wassenaar

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
hvfalcao
Creator
Creator
Author

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.


talk is cheap, supply exceeds demand
gardenierbi
Creator
Creator

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

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.


talk is cheap, supply exceeds demand