Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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;
This can't work as if you have a purchase in 2010, the sum will be 10 and flag will be Y...
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;
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
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)
Hi Ruben,
i was trying to do it inside the script, I will try also your idea soon!! Thanks!!
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
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.