Client fidelity check

[#1]

Hi guys,

I'm trying to create a script for qlikview but I have some problems.

I'd like to check if a customer bought items last four years (at least one per year). What I have right now:

-db table1 with ID_customer

-db table2 with ID_customer, purchase, date of purchase

Es:

table1

ID_customer

1

3

5

7

10

12

14

15

table2

ID_customer purchase date

1 car 12/01/2011

1 tires 12/01/2012

1 motor 18/04/2013

1 glass 19/09/2014

1 tires 12/01/2012

5 parfum 05/07/2009

10 water 30/11/2014

What I'd like create is a list with customers that bought last four years, in this example ID_customer 1

Can anyone help me?

Cheers,

Diego

[rubenmarin]

Hi you can create a list box with the expression:

=Aggr(DISTINCT Only({<date={'>$(=AddMonths(Today(), -48))'}>} ID_customer), ID_customer)

[#3]

Hi,

thanks for the answer, but it's not what I'm trying to do. What I need is to know people that bought at least one item for each year (of the past X years). In your example, you find ID 1 (correct) and ID 10 that bought only last year

[agomes1971]

Hi,

is this can be a start for you?

Regards

André Gomes

[rubenmarin]

Ok, then maybe something like:

=Aggr(If(Count({<date={'>$(=AddMonths(Today(), -48))'}>} DISTINCT Year(date))=4, ID_customer), ID_customer)

[consenit]

Hi there.

I think you can do all the calculations on the script. Try this:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

  12

  14

  15

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, date

  1, car, 12/01/2011

  1, tires, 12/01/2012

  1, motor, 18/04/2013

  1, glass, 19/09/2014

  1, tires,12/01/2012

  5, parfum, 05/07/2009

  10, water, 30/11/2014

  1, glass, 07/01/2015

];

PurchaseByYear:

LOAD

  ID_customer,

  YEAR(date) as year,

  IF(YEAR(date) >= YEAR(TODAY())-4, 1, 0) AS FlagLastYears,

  COUNT(purchase) as TotalPurchase

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) > YEAR(TODAY()-4), 1, 0);

ClientFidelity:

LOAD

  ID_customer,

  SUM(FlagLastYears) AS Fidelity

RESIDENT PurchaseByYear

WHERE TotalPurchase > 0

GROUP BY ID_customer;

LEFT JOIN (Customers) LOAD

  ID_customer,

  IF(Fidelity >= 4, 1, 0) AS Fidelity

RESIDENT ClientFidelity;

DROP TABLE ClientFidelity;

DROP TABLE PurchaseByYear;

This will generate a flag field "Fidelity" in the customers table signaling if such customer purchased something in the past 4 years including current year.

Kind Regards,

Ernesto.

[#7]

Hi Ernesto,

thanks for the reply but it seems not to be working.. If you try to change

1, glass, 07/01/2015

to

1, glass, 07/01/2014,

no customers should have the flag Fidelity settet to one (as no one bought on 2015).. But it still happens...

[agomes1971]

Hi Diego,

i hope my answer as in somehow helped you.

Regards

André Gomes

[consenit]

Yes, I had to add an entry for at least one purchase in 2015 to make it work because my original calculation was based on the TODAY() function wich returns the current date. Don't worry: it's easy to fix though:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

  12

  14

  15

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, date

  1, car, 12/01/2011

  1, tires, 12/01/2012

  1, motor, 18/04/2013

  1, glass, 19/09/2014

  1, tires,12/01/2012

  5, parfum, 05/07/2009

  10, water, 30/11/2014

];

MaxYear:

LOAD

  MAX(YEAR(date)) AS MaxYear

RESIDENT Purchase;

LET vMaxYear =FLOOR(PEEK('MaxYear',0));

DROP TABLE MaxYear;

PurchaseByYear:

LOAD

  ID_customer,

  YEAR(date) as year,

  IF(YEAR(date) >= $(vMaxYear) -4, 1, 0) AS FlagLastYears,

  COUNT(purchase) as TotalPurchase

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) > YEAR(TODAY()-5), 1, 0);

ClientFidelity:

LOAD

  ID_customer,

  SUM(FlagLastYears) AS Fidelity

RESIDENT PurchaseByYear

WHERE TotalPurchase > 0

GROUP BY ID_customer; 

LEFT JOIN (Customers) LOAD

  ID_customer,

  IF(Fidelity >= 4, 1, 0) AS Fidelity

RESIDENT ClientFidelity;

DROP TABLE ClientFidelity;

DROP TABLE PurchaseByYear;

Kind regards,

Ernesto.

[#10]

Hi Ernesto,

what I was saying is that if you remove the 2015 date in your previous script, it still gives you a flag value setted to 1 for the customer (I think it's an error). Anyway I can study and work using the code you posted, so thank you so much!!