Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I made a customer distribution a while ago (loyal, new, stopped). Now it is true that I determined this on the basis of months (see script). Now I want to do this on the basis of weeks (5 weeks) instead of months because this is somewhat more robust and can be extended or shortened by a week. Can someone tell me how the script should look like?
Below the script that I now use:
//CustomerGrouping
CustomerClassesTemp:
Load Distinct
%DebiteurId
Resident DebiteurEFEP;
////Creates flag for Customers with an order this month
Left Join (CustomerClassesTemp)
Load
%DebiteurId,
'1' as HasOrdersThisMonthFlag
Resident Feitentabel
Where %DatumId >= MonthStart(Today());
////Creates flag for Customers with an order in a prior month
Left Join (CustomerClassesTemp)
Load
%DebiteurId,
'1' as HasPastOrdersFlag
Resident Feitentabel
Where %DatumId < Date(MonthStart(Today()));
Left Join(DebiteurEFEP)
Load
%DebiteurId,
If(HasOrdersThisMonthFlag = '1' and Isnull(HasPastOrdersFlag),'New Customer',
if(HasOrdersThisMonthFlag = '1' and HasPastOrdersFlag = '1','Loyal Customer',
if(HasPastOrdersFlag = '1' and isnull(HasOrdersThisMonthFlag),'Stopped Customer'))) as [Customer Group]
Resident CustomerClassesTemp;
Drop Table CustomerClassesTemp;
Thanks in advance!
Hi,
for adding weeks, you can try multiplying the number of weeks by the number of days on each weeek (7), like this:
for 5 weeks:
Where %DatumId < Date(Today()+(7*5));
Hi,
maybe I miss something,
if you replace use of MonthStart() by WeekStart()
doesn't work ?
regards
Hi,
for adding weeks, you can try multiplying the number of weeks by the number of days on each weeek (7), like this:
for 5 weeks:
Where %DatumId < Date(Today()+(7*5));
I correct
if you want 5 weeks :
WeekStart() - 28
current week + 4 back
thankyou!
also thanks! it did work out!
You're welcome, good luck