topic Re: Client fidelity check in QlikView

Client fidelity check

Wed, 07 Jan 2015 11:48:27 GMT

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

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

Re: Client fidelity check

rubenmarin — Wed, 07 Jan 2015 12:02:53 GMT

Hi you can create a list box with the expression:

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

Re: Client fidelity check

Wed, 07 Jan 2015 12:08:55 GMT

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

Re: Client fidelity check

agomes1971 — Wed, 07 Jan 2015 12:16:52 GMT

Hi,

is this can be a start for you?

Regards

André Gomes

Re: Client fidelity check

rubenmarin — Wed, 07 Jan 2015 12:18:41 GMT

Ok, then maybe something like:

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

Re: Client fidelity check

consenit — Wed, 07 Jan 2015 12:44:48 GMT

Hi there.

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

Customers:

LOAD * INLINE

[

ID_customer

];

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.

Re: Client fidelity check

Wed, 07 Jan 2015 14:12:58 GMT

Hi Ernesto,

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

1, glass, 07/01/2015

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

Re: Client fidelity check

agomes1971 — Wed, 07 Jan 2015 14:20:09 GMT

Hi Diego,

i hope my answer as in somehow helped you.

Regards

André Gomes

Re: Client fidelity check

consenit — Wed, 07 Jan 2015 14:35:07 GMT

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

];

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.

Re: Client fidelity check

Wed, 07 Jan 2015 14:41:18 GMT

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!!

Re: Client fidelity check

martin_dideriks — Wed, 07 Jan 2015 14:50:38 GMT

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)

Re: Client fidelity check

anbu1984 — Thu, 08 Jan 2015 05:51:58 GMT

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;

Re: Client fidelity check

Thu, 08 Jan 2015 10:16:52 GMT

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

Re: Client fidelity check

anbu1984 — Thu, 08 Jan 2015 11:26:33 GMT

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;

Re: Client fidelity check

jonathandienst — Thu, 08 Jan 2015 11:41:33 GMT

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

Re: Client fidelity check

rubenmarin — Thu, 08 Jan 2015 12:06:09 GMT

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)

Re: Client fidelity check

Thu, 08 Jan 2015 12:59:25 GMT

Hi Ruben,

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

Re: Client fidelity check

Thu, 08 Jan 2015 16:09:56 GMT

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

Re: Client fidelity check

consenit — Thu, 08 Jan 2015 16:33:58 GMT

Hi there.

Try this:

Customers:

LOAD * INLINE

[

ID_customer

];

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.