11 Replies Latest reply: Oct 1, 2013 8:13 AM by RUBEN TAPIA RSS

    How to do this??

    Eduard Cabanas Gili

      Hi,

       

      I would like to cluster my customers as a "Superloyal" if a Customer bought at minimum once order every month last 3 months.

       

      I have the following table and I would like that the script fill the field "Superloyal" and I have no idea how to do it...

       

      Thank's

       

      Eduard

       

      CUSTACCOUNT_CONTADORDATEFINANCIAL_CONTADORSuperLoyal
      C00000231/08/2012No
      C00000231/10/2012No
      C00000231/12/2012No
      C00000231/01/2013No
      C00000228/02/2013Yes
      C00000231/03/2013Yes
      C00000230/04/2013Yes
      C00000231/05/2013Yes
      C00000231/07/2013No
      C00000231/08/2013No
      C00000230/09/2013Yes
      C00001431/05/2012No
      C00001431/07/2012No
      C00001431/08/2012No
      C00001430/09/2012Yes
      C00001431/10/2012Yes
      C00001430/11/2012Yes
      C00001431/01/2013No
      C00001428/02/2013No
      C00001431/03/2013Yes
      C00001430/04/2013Yes
      C00001431/05/2013Yes
      C00001431/07/2013No
      C00001431/08/2013No
        • Re: How to do this??
          Luciano Assad

          If you want to create this field inside the load you can do it like this, but you will loose your date flexibility:

           

          LET vMonths=addmonths(today(),-3); //This will create the begin date "Today -3 months".

           

          TableName:
          LOAD

          CUSTACCOUNT_CONTADOR,

          DATEFINANCIAL_CONTADOR

          if(DATEFINANCIAL_CONTADOR>=$(vMonths),'Yes','No') as SuperLoyal

          FROM TableName.qvd;


          //Allways when you gonna use function inside a load, declare it inside a variable, that way qlikview don't //will be running the function row by row.



          You can do it at the app.


          Create a var


           

          =if(GetSelectedCount(DateField)>=1,only(DateField),today())

           


          And you will need to create a Calculated Dimension.


           

          if(DATEFINANCIAL_CONTADOR>=$(varAbove),'Yes','No') as SuperLoyal

           




           

           

          • Re: How to do this??
            Manas BN

            Hi Eduard,


            I think this is a similar question you had asked earlier - http://community.qlik.com/thread/93970

             

            You can try the below:

            //Raw Data

            TEMP_Fact:

            LOAD *                                    // Create Month Field

            ,MonthName(date) as Month

            INLINE [

            Salesid,     custaccount,     date

            1, A, 30/06/2013

            2, B, 30/07/2013

            3, A, 15/07/2013

            4, B, 31/08/2013

            5, A, 15/09/2013

            6, B, 24/04/2013

            7, A, 30/07/2013

            8, C, 23/03/2013

            8, C, 23/03/2013

            9, C, 25/06/2013

            10, C, 23/07/2013

            11, C, 23/08/2013

            ];

             

            //// Calculate No of Months and Sales in last 90 days

            LEFT JOIN (TEMP_Fact)

            LOAD

            custaccount

            ,count(distinct Month) as NoOfMonths

            ,count(custaccount) as [Sales (last 90 days)]

            RESIDENT TEMP_Fact

            //Last 90 days only

            Where num(date)>=num(today())-90

            group BY custaccount ;

             

            Fact:

            NoConcatenate

            Load Salesid, custaccount, date, Month,

              // Sales for the last 90 days

              if((num(date)>=num(today())-90),[Sales (last 90 days)],0) as [Sales (last 90 days)],

              if(NoOfMonths>=3,'Super Loyal Customer',null()) as LoyalCustomerFlag

            Resident TEMP_Fact;

             

            drop table TEMP_Fact;

             

            Result:

            Capture.PNG.png

            • Re: How to do this??
              Clever Anjos

              Hi Eduard,

              Check if this app solve your requirements.

               

              I´ve implemented a logic into set analysis, because a customer can be superloyal this month and not next month, am I Right?

                • Re: How to do this??
                  Eduard Cabanas Gili

                  Hi Clever,

                   

                  We are very very close, but the calculations are not doing well what we want. To be a Superloyal the customer has to be bought at least once PER month, if you buy 2 orders last month, but the month-3=cero, you are not superloyal...if you buy one month, one mont-2 and one month-3 you are a superloyal ;-)

                   

                  And.....has to move with the month calendar....very very trcicky code

                   

                   

                  Thank you very very very much Clever

                   

                  Regards from Barcelona

                • Re: How to do this??
                  bobbyraj santhiogu

                  Hi,

                  Try this:

                   

                  FACT_tmp:
                  LOAD

                  *,
                  CUSTACCOUNT_CONTADOR&DATEFINANCIAL_CONTADOR as %KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR

                  ;
                  LOAD * INLINE [
                  CUSTACCOUNT_CONTADOR, DATEFINANCIAL_CONTADOR
                  C000002, 31/08/2012
                  C000002, 31/10/2012
                  C000002, 31/12/2012
                  C000002, 31/01/2013
                  C000002, 28/02/2013
                  C000002, 31/03/2013
                  C000002, 30/04/2013
                  C000002, 31/05/2013
                  C000002, 31/07/2013
                  C000002, 31/08/2013
                  C000002, 30/09/2013
                  C000014, 31/05/2012
                  C000014, 31/07/2012
                  C000014, 31/08/2012
                  C000014, 30/09/2012
                  C000014, 31/10/2012
                  C000014, 30/11/2012
                  C000014, 31/01/2013
                  C000014, 28/02/2013
                  C000014, 31/03/2013
                  C000014, 30/04/2013
                  C000014, 31/05/2013
                  C000014, 31/07/2013
                  C000014, 31/08/2013
                  ]
                  ;

                  JOIN

                  LOAD

                  CUSTACCOUNT_CONTADOR,
                  DATEFINANCIAL_CONTADOR,
                  MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)) as DATEFINANCIAL_CONTADOR2,
                  '1'
                  as Flag

                  RESIDENT

                  FACT_tmp
                  Where
                  Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)))
                  ;

                  JOIN

                  LOAD
                  CUSTACCOUNT_CONTADOR,
                  DATEFINANCIAL_CONTADOR,
                  MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)) as DATEFINANCIAL_CONTADOR3,
                  '1'
                  as Flag2

                  RESIDENT

                  FACT_tmp
                  Where

                  Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)))
                  ;

                  FACT:
                  LOAD

                  CUSTACCOUNT_CONTADOR,
                  DATEFINANCIAL_CONTADOR,
                  if(Flag+Flag2=2,'SuperLoyal') as SuperLoyal

                  RESIDENT

                  FACT_tmp;

                  DROP TABLE FACT_tmp;

                   

                  It should do exactly what you need.

                    • Re: Re: How to do this??
                      Eduard Cabanas Gili

                      Hi

                       

                      I tryed your code but when I uploaded some new data the calculations did not goes well, see the project attached and please, could you take a look if something is wrong

                       

                      Thank you very much

                       

                      Eduard

                        • Re: How to do this??
                          bobbyraj santhiogu

                          Hi,

                           

                          I thought that all your dates where month end date but you have all kind of dates, that is why it didn't work.

                          Try this, it works fine:

                           

                           

                          FACT_tmp:

                          LOAD

                          *,
                          CUSTACCOUNT_CONTADOR&month(DATEFINANCIAL_CONTADOR)&year(DATEFINANCIAL_CONTADOR) as %KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR;
                          LOAD * INLINE [
                          CUSTACCOUNT_CONTADOR, DATEFINANCIAL_CONTADOR
                          C000014,29/09/2011
                          C000014,17/11/2011
                          C000014,20/11/2011
                          C000014,22/01/2012
                          C000014,01/04/2012
                          C000014,11/05/2012
                          C000014,17/05/2012
                          C000014,10/07/2012
                          C000014,21/08/2012
                          C000014,22/09/2012
                          C000014,26/10/2012
                          C000014,17/11/2012
                          C000014,08/01/2013
                          C000014,28/02/2013
                          C000014,18/03/2013
                          C000014,01/04/2013
                          C000014,10/04/2013
                          C000014,12/05/2013
                          C000014,20/05/2013
                          C000014,23/05/2013
                          C000014,01/07/2013
                          C000014,27/08/2013
                          C000002,17/10/2011
                          C000002,28/12/2011
                          C000002,28/12/2011
                          C000002,02/08/2012
                          C000002,17/08/2012
                          C000002,26/10/2012
                          C000002,12/12/2012
                          C000002,22/12/2012
                          C000002,04/01/2013
                          C000002,25/01/2013
                          C000002,15/02/2013
                          C000002,13/03/2013
                          C000002,01/04/2013
                          C000002,19/04/2013
                          C000002,13/05/2013
                          C000002,30/05/2013
                          C000002,04/07/2013
                          C000002,13/08/2013
                          C000002,16/09/2013
                          C000014,29/09/2011

                          ]
                          ;


                          JOIN

                          LOAD

                          CUSTACCOUNT_CONTADOR,
                          DATEFINANCIAL_CONTADOR,
                          MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)) as DATEFINANCIAL_CONTADOR2,
                          '1'
                          as Flag

                          RESIDENT

                          FACT_tmp
                          Where
                          Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)))
                          ;

                          JOIN

                          LOAD
                          CUSTACCOUNT_CONTADOR,
                          DATEFINANCIAL_CONTADOR,
                          MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)) as DATEFINANCIAL_CONTADOR3,
                          '1'
                          as Flag2

                          RESIDENT

                          FACT_tmp
                          Where

                          Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)))
                          ;

                          FACT:
                          LOAD

                          CUSTACCOUNT_CONTADOR,
                          DATEFINANCIAL_CONTADOR,
                          if(Flag+Flag2=2,'SuperLoyal') as SuperLoyal

                          RESIDENT

                          FACT_tmp;

                          DROP TABLE FACT_tmp;

                      • Re: How to do this??

                        Eduard:

                         

                        Try this attached model.

                         

                        The above script is made to compare just the last 5 dates (starting from this month), however, it is very easy to change it if you need more to compare. You would just need to:

                        a) Change the corresponding "TO value" in both FOR clauses

                        b) Add as many new columns as needed in the CROSSTABLE (next one would be f_status(DAY4, DAY5, DAY6) as '[UPTO $(v_day6)]', etc)

                         

                        Let me know if this helps.

                         

                        RTapia

                          • Re: Re: How to do this??
                            Eduard Cabanas Gili

                            Hi Ruben

                             

                            The problem is not to look for the sales accum, the calculation is:

                             

                            present month has bought? Yes

                            present month -1 has bought? Yes

                            present month -2 has bought? Yes   then super loyal

                             

                            if somebody has bough 2 last month but not the mont-2 is not a superloyal

                             

                            Thank you very much for your help...this is a very tricky problem.

                             

                            Eduard

                              • Re: How to do this??

                                Eduard:

                                 

                                I did not accumulate the sales. I simply mark each month with an "X" if there were sales or not.

                                 

                                Please check again, because based on your data, it's working as you expect, that is, for the last five dates, only C000002 is considered SuperLoyal in September (it's considered NOT superloyal in august for example, since in June there were no sales).

                                 

                                Awaiting your feedback,

                                 

                                 

                                Regards

                                 

                                Rtapia