Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
Hi Kushal,
I am executing it but I can't see any values in the Flag.
Thank you,
Vivek
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
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;
Hi Kushal,
If I want to use thin in chart then in expressions what shall I use?
Thanks,
Vivek