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

    Week Start

    Kumar Pramod

      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
          Andrew Mein

          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
              Kumar Pramod

              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)

                • Re: Week Start
                  Andrew Mein

                  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
                      Kumar Pramod

                      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
                          Andrew Mein

                          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
                              Kumar Pramod

                              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
                                  Andrew Mein

                                  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

                              • Re: Week Start
                                Andrew Mein

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

                                  • Re: Week Start
                                    Kumar Pramod

                                    Sorry dint get you?

                                    please elaborate?

                                      • Re: Week Start
                                        Andrew Mein

                                        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.

                                          • Re: Week Start
                                            Kumar Pramod

                                            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