Count of Customer Frequency

[Anonymous]

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

[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;

[swuehl]

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

[Anonymous]

Hi Kushal,

I am executing it but I can't see any values in the Flag.

Thank you,

Vivek

[Anonymous]

Hi Swuehl,

I have used it in my code for creating in the script but it is not creating any value for the OrderFreqGroup. Can you help me how to figure it

Thanks,

Vivek

[swuehl]

Maybe your dates are not properly interpreted, try

SET DateFormat = 'DD/MM/YYYY';

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;

[Anonymous]

Hi Kushal,

If I want to use thin in chart then in expressions what shall I use?

Thanks,

Vivek