Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a question about Qliksense.
I would like to divide my customers into 3 groups:
New customers (customers who have bought this month and never before)
Loyal customers (customers who bought this month and for that too)
Stopped customers (customers who have not bought this month and always dit before)
I do have a master's calendar but in spite of that I do not know how to do this. Does anyone have any idea?
thanks in advance!
With best regards,
Willem van der Plas
There are probably several ways of doing this but give something like this a shot.
///Lists your customers
CustomerClassesTemp:
Load Distinct
CustomerId
Resident Customers;
////Creates flag for Customers with an order this month
Left Join (CustomerClassesTemp)
Load
CustomerId,
'1' as HasOrdersThisMonthFlag
Resident OrderDetails
Where OrderDate >= MonthStart(Today());
////Creates flag for Customers with an order in a prior month
Left Join (CustomerClassesTemp)
Load
CustomerId,
'1' as HasPastOrdersFlag
Resident OrderDetails
Where OrderDate < Date(MonthStart(Today()));
Left Join(Customers)
Load
CustomerId,
If(HasOrdersThisMonthFlag = '1' and Isnull(HasPastOrdersFlag),'New Customer',
f(HasOrdersThisMonthFlag = '1' and HasPastOrdersFlag = '1','Loyal Customer',
if(HasPastOrdersFlag = '1' and isnull(HasOrdersThisMonthFlag),'Stopped Customer'))) as [Customer Group]
Resident CustomerClassesTemp;
Drop Table CustomerClassesTemp;
There are probably several ways of doing this but give something like this a shot.
///Lists your customers
CustomerClassesTemp:
Load Distinct
CustomerId
Resident Customers;
////Creates flag for Customers with an order this month
Left Join (CustomerClassesTemp)
Load
CustomerId,
'1' as HasOrdersThisMonthFlag
Resident OrderDetails
Where OrderDate >= MonthStart(Today());
////Creates flag for Customers with an order in a prior month
Left Join (CustomerClassesTemp)
Load
CustomerId,
'1' as HasPastOrdersFlag
Resident OrderDetails
Where OrderDate < Date(MonthStart(Today()));
Left Join(Customers)
Load
CustomerId,
If(HasOrdersThisMonthFlag = '1' and Isnull(HasPastOrdersFlag),'New Customer',
f(HasOrdersThisMonthFlag = '1' and HasPastOrdersFlag = '1','Loyal Customer',
if(HasPastOrdersFlag = '1' and isnull(HasOrdersThisMonthFlag),'Stopped Customer'))) as [Customer Group]
Resident CustomerClassesTemp;
Drop Table CustomerClassesTemp;
Thankyou! It works