6 Replies Latest reply: May 26, 2016 2:08 AM by vivek Moningi RSS

    Count of Customer Frequency

    vivek Moningi

      Hi Guys,

       

      I have a requirement to count the frequency of the customer and categories it into 3 categories:

      For Example:

      Order Date | Customer Name

      21/01/2009 | a

      12/02/2009 | a

      08/04/2009 | a

      12/05/2009 | a

      28/05/2009 | a

      so what I need to do , a customer  have ordered a product in 21/01/2009 and he have ordered again within 30 days then it should be categorized to "Within 30 Days" and again the same customer have ordered again within  31 to 90 Days then it should be categorized into "1 month to 3 Months" and again if the customer orders again after 90 days it should be categorized into "> 3 Months".

      I have used an expression for that :

      "if(Aggr(Num(Max([Order Date]))-Num(Min([Order Date])),[Customer Name],Year)<=30,'Within 30 Days',

      if(Aggr(Num(Max([Order Date]))-Num(Min([Order Date])),[Customer Name],Year)>=31 and Aggr(Num(Max([Order Date]))-Num(Min([Order Date])),[Customer Name],Year)<=90,'1 to 3 Months',

      if(Aggr(Num(Max([Order Date]))-Num(Min([Order Date])),[Customer Name],Year)>=91 ,'>3 Months')))"

      But the problem is it is only calculating only 1 value instead of 4 values.

      Can anyone help me with this it is bit urgent


      Thanks in advance,

      Vivek




       

        • Re: Count of Customer Frequency
          kushal chawda

          Try this

           

          Data:

          LOAD OrderDate,

                    Customer

          FROM Table;

           

          New:

          noconcatenate

          LOAD *,

                    if(Customer=previous(Customer)  and

                    (if( (floor(OrderDate) - floor(previous(OrderDate ))) <= 30,'Within 30 Days',

                    if( (floor(OrderDate) - floor(previous(OrderDate ))) > 30 and (floor(OrderDate) - floor(previous(OrderDate ))) > 30,'1 Month to 3 Months','> 3 Months')))) as Flag

          resident Data

          order by Customer,OrderDate;

           

          drop table Data;

          • Re: Count of Customer Frequency
            Stefan Wühl

            what if you create a field for grouping in the script:

             

            LOAD OrderDate, CustomerName,

                      If(CustomerName = Peek('CustomerName'),

                           If(OrderDate - Peek('OrderDate') <= 30, 'Within 30 Days',

                                If(OrderDate - Peek('OrderDate') <= 90, '1 to 3 Months','>3 Months'))) as OrderFreqGroup

            RESIDENT YourTable

            GROUP BY CustomerName, OrderDate;


            Then you can group your orders by OrderFreqGroup.



            edit: Sorry, haven't seen Kushals answer before posting