Week Start

[#1]

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

[#2]

your first expression:

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.

[#3]

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

[#4]

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