18 Replies Latest reply: Jan 8, 2015 11:33 AM by Ernesto García RSS

    Client fidelity check

    Diego Da Ros

      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

        • Re: Client fidelity check
          Ruben Marin

          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
            André Gomes

            Hi,

             

            is this can be a start for you?

             

            Regards

             

            André Gomes

            • Re: Client fidelity check
              Ernesto García

              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.

                • Re: Client fidelity check
                  Diego Da Ros

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

                    • Re: Client fidelity check
                      André Gomes

                      Hi Diego,

                       

                      i hope my answer as in somehow helped you.

                       

                      Regards

                       

                      André Gomes

                      • Re: Client fidelity check
                        Ernesto García

                        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.

                      • Re: Client fidelity check
                        Diego Da Ros

                        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
                            Ernesto García

                            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.

                        • Re: Client fidelity check
                          Martin Dideriksen

                          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
                            anbu cheliyan

                            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
                                Diego Da Ros

                                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
                                    anbu cheliyan

                                    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
                                  Jonathan Dienst

                                  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