1 Reply Latest reply: Sep 27, 2013 3:07 AM by Manas BN RSS

    How to do this? tricky query

    Eduard Cabanas Gili

      Hi,

       

      I have a database with the revenues of every customer, I would like to know what I consider a super loyal customer: The customer that has bought us once or more evry month last 3 months.

       

      The Data base has the folling structure:

       

      Salesid     custaccount     date

      1               A                    31/06/2013

      2               B                    30/07/2013

      3               A                    15/07/2013

      4               B                    31/08/2013

      5               A                    15/09/2013

       

      Superloyal Cutomer: A with 3 sales once as minimum every months last 90 days

       

      *Date= Today

       

      Thanks guys

        • Re: How to do this? tricky query
          Manas BN

          Hi Eduard,

           

          May be this will work?

           

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