Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thewillemzelluf
Creator
Creator

different types of customers

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!

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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));

View solution in original post

6 Replies
ogautier62
Specialist II
Specialist II

Hi,

maybe I miss something,

if you replace use of MonthStart() by WeekStart()

doesn't work ?

regards

YoussefBelloum
Champion
Champion

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));

ogautier62
Specialist II
Specialist II

I correct

if you want 5 weeks :

WeekStart() - 28

current week + 4 back

thewillemzelluf
Creator
Creator
Author

thankyou!

thewillemzelluf
Creator
Creator
Author

also thanks! it did work out!

YoussefBelloum
Champion
Champion

You're welcome, good luck