Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
Make sure the date formatting matches e.g. try Date(WEEKEND(MONTHSTART(CanonicalDate),0,4),'YYYY-MM-DD')
assuming CanonicalDate is in that format
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.
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
You are checking CanonicalDate against the month start and month end of CanonicalDate - it will always be within those bounds
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)
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
Sorry dint get you?
please elaborate?