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

[martin_dideriks]

I don't know if you define the last 4 years as current year+previous 3 years or simple as the previous 48 months.

The example is based on years and should get you started (just change the time interval to whatever you need).

Count({$<Year={">=$(=Max(Year)-3)<=$(=Max(Year))"}>} DISTINCT Year)

[anbu1984]

Purchase:

LOAD ID_customer, purchase, Date#(date,'DD/MM/YYYY') As date 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

];

Load ID_customer,If(Sum(DISTINCT Year(Today())-Year(date))=10,'Y','N') As PresentFlag Resident Purchase Group By ID_customer;

[#4]

This can't work as if you have a purchase in 2010, the sum will be 10 and flag will be Y...

[anbu1984]

LET vLastFourYears =  (Year(Today())-1) & ';' & (Year(Today())-2) & ';' & (Year(Today())-3) & ';' & (Year(Today())-4);

Purchase:

LOAD ID_customer, purchase, Date#(date,'DD/MM/YYYY') As date INLINE

[

  ID_customer, purchase, date

  1, car, 12/01/2011

  1, car, 12/01/2010

  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

];

Load ID_customer,If(Count(DISTINCT If(Index('$(vLastFourYears)',Year(date)),Year(date)))=4,'Y','N') As PresentFlag Resident Purchase Group By ID_customer;

[jonathandienst]

Hi

you can use this in a list box or as a calculated dimension:

=Aggr(If(Count(DISTINCT Year([purchase date])) >= 4, ID_Customer), ID_Customer)

HTH

Jonathan

[rubenmarin]

Hi Diego, my expresion didn't worked?

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

If you want full years you can use:

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

[#8]

Hi Ruben,

i was trying to do it inside the script, I will try also your idea soon!! Thanks!!

[#9]

Hi Ernesto,

sorry to bother you.. What have I to do if I want also how much a client spent every year in exam? (I'd like to know, if a person bought something every year of the last four year and how much he spent every year) Starting from a table like this:

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, value, date

  1, car, 10000, 12/01/2011

  1, tires, 400, 12/01/2012

  1, motor, 5000, 18/04/2013

  1, glass, 300, 19/09/2014

  1, tires, 400, 12/01/2012

  5, parfum, 80, 05/07/2009

  10, water, 10, 30/11/2014

];

Thank you in advance for your help

[consenit · Accepted solution]

Hi there.

Try this:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, value, date

  1, car, 10000, 12/01/2011

  1, tires, 400, 12/01/2012

  1, motor, 5000, 18/04/2013

  1, glass, 300, 19/09/2014

  1, tires, 400, 12/01/2012

  5, parfum, 80, 05/07/2009

  10, water, 10, 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,

  SUM(value) as TotalAmount

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) >  $(vMaxYear) -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;

It's basically the same script with an added SUM() of all the values by year and without the last DROP TABLE statement.

Kind regards,

Ernesto.