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

Count of Customer Frequency

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

Vivek

• Re: Count of Customer Frequency

Try this

Data:

Customer

FROM Table;

New:

noconcatenate

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

Hi Kushal,

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

Thank you,

Vivek

• Re: Count of Customer Frequency

Maybe your dates are not properly interpreted, try

SET DateFormat = 'DD/MM/YYYY';

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;

• Re: Count of Customer Frequency

Hi Kushal,

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

Thanks,

Vivek

• Re: Count of Customer Frequency

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

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

• Re: Count of Customer Frequency

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