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]

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

[#3]

Hi Thanks for the reply.

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)

[#4]

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

assuming CanonicalDate is in that format

[#5]

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.

[#6]

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

[#7]

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

[#8]

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)

[#9]

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)

added MAX() to CanonicalDate

[#10]

Sorry dint get you?

please elaborate?