Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

moningi13579
New Contributor III

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


Thanks in advance,

Vivek




6 Replies

Re: Count of Customer Frequency

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;

MVP
MVP

Re: Count of Customer Frequency

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

moningi13579
New Contributor III

Re: Count of Customer Frequency

Hi Kushal,

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

Thank you,

Vivek

moningi13579
New Contributor III

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

MVP
MVP

Re: Count of Customer Frequency

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;

moningi13579
New Contributor III

Re: Count of Customer Frequency

Hi Kushal,

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

Thanks,

Vivek

Community Browser