12 Replies Latest reply: Mar 11, 2016 6:22 AM by Andrew Mein

# Week Start

Hi all,

As i have a two campaigns one start on Monday and ends on Sunday.

and one starts on Thursday and ends on Wednesday.

Now i am calculating monthly data from first Monday to last Sunday of the month using below load Script

Date(MonthStart(weekend(CanonicalDate)),'MMM-YY') as MonthPeriod1

Expression:

count({\$<MonthPeriod1= {"\$(=Date(MonthStart(weekend(Max(CanonicalDate))),'MMM-YY'))"},

DateType= {'invitation'},batch_meta_data_id = {'35'}>} invitation_id)

Now i have to calculate Monthly data for second campaign which starts on Thursday so i need Data from first Thursday to last Wednesday of the month. How can i achieve this without affecting the first values.

Regards,

Pramod

• ###### Re: Week Start

WEEKEND lets you define the first day of the week, so:

WEEKEND(MONTHSTART(CanonicalDate),0,4)

will give you the first Thursday of the month

WEEKSTART(MONTHEND(CanonicalDate),0,2)

will give you the last Wednesday

• ###### Re: Week Start

I used expression like below but it's not working

count({\$<CanonicalDate= {">=\$(Date(WEEKEND(MONTHSTART(CanonicalDate),0,4)))<=\$(Date(WEEKSTART(MONTHEND(CanonicalDate),0,2)))"},

DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

• ###### Re: Week Start

Make sure the date formatting matches e.g. try Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'YYYY-MM-DD')

assuming CanonicalDate is in that format

• ###### Re: Week Start

I have Canonical date in format MM-DD-YYYY,

so i am using like below:

count({\$<CanonicalDate= {">=\$(Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=\$(Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),,'MM-DD-YYYY'))"},

DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Instead of getting the value of present month it's fetching the whole data of that batch.

• ###### Re: Week Start

try:

count({\$<CanonicalDate= {">=\$(=Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=\$(=Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),,'MM-DD-YYYY'))"},

DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

Note the additional = within the \$ notation

• ###### Re: Week Start

It's not working.... returning zero value

count({\$<CanonicalDate= {">=\$(=Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'MM-DD-YYYY'))<=\$(=Date(WEEKSTART(MONTHEND(CanonicalDate),0,2),'MM-DD-YYYY'))"},

DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

• ###### Re: Week Start

assuming you want the most recent months data (as your first set analysis)

count({\$<CanonicalDate= {">=\$(=Date(WEEKEND(MONTHSTART(MAX(CanonicalDate)),0,4),'MM-DD-YYYY'))<=\$(=Date(WEEKSTART(MONTHEND(MAX(CanonicalDate)),0,2),'MM-DD-YYYY'))"},

DateType= {'invitation'},batch_meta_data_id = {'24'}>} invitation_id)

• ###### Re: Week Start

You are checking CanonicalDate against the month start and month end of CanonicalDate - it will always be within those bounds

• ###### Re: Week Start

Sorry dint get you?

• ###### Re: Week Start

count({\$<MonthPeriod1= {"\$(=Date(MonthStart(weekend(Max(CanonicalDate))),'MMM-YY'))"},

DateType= {'invitation'},batch_meta_data_id = {'35'}>} invitation_id)

whereas the other one didn't use MAX()

Therefore, if CanonicalDate was 03-10-2016, the first Thursday would be 03-03-2016 and last Wednesday 03-30-2016, meaning it was within the range.

Then if the next record had CanonicalDate = 02-18-2016, it would check against 02-04-2016 and 02-24-2016 - again within the range.

Using MAX, would mean the range would be 03-03-2016 and 03-30-2016 for both dates - 03-10-2016 would be in the range. 02-18-2016 would not be in the range.

• ###### Re: Week Start

Thanks a lot Andrew,

We are using MonthPeriod1 because we have a scenario where if the campaign starts in last month and ends in current month then the values of that campaign is added to present month.

For ex: In feb -16 the month start date is taken as 01-feb and month end is taken as 28-feb.

In Mar-16 the month start date is taken as 29-feb and month end is taken as 27-mar.

Same way for thursday campaign: I need to achieve below mentioned

In feb-16 the month start is 28-jan and month end is 24-feb

In mar-16 the month start is 25-feb and month end is 30-mar

• ###### Re: Week Start

change the WEEKEND(MONTHSTART(CanonicalDate),0,4) to WEEKSTART(MONTHSTART(CanonicalDate),0,3) to give the first Thursday before the start of the month.