3 Replies Latest reply: Mar 28, 2013 2:36 PM by Stefan Wühl RSS

    Capture the 2nd week of every month dynamically

    Melinda Bruck

      I have the attached qvw that creates our business calendar based on broadcast calendar. This broadcast calender is then used in all our qvws.   I need to dynamically capture the 2nd week of every month so when you open any qvw the month selection in the filter is based on the month that is in the 2nd week of the broadcast month.  Any suggestions on how get the second week of the broadcast month dynamically? 

        • Re: Capture the 2nd week of every month dynamically
          Stefan Wühl

          You already have a consecutive BroadcastWeek and a BroadcastYearMonth field. So it should be enough to add a line to your Broadcast calendar table:

           

          BroadcastCalendar:

          NoConcatenate LOAD

              BroadcastWeekStart,

              BroadcastWeekEnd,

              //BroadcastWeekNo,

              Year,

              Month,

               BroadcastMonthYear,

               BroadcastPeriod,

               BroadcastYearWeek,

               BroadcastQuarter,

               BroadcastWeek,

               CalendarKey,

              autonumber(BroadcastWeek, BroadcastMonthYear) as BroadcastWeekInMonth

          RESIDENT

              BroadcastCalendar_X

          WHERE

              Year <= Year(today()) + 1

          ;

           

          Then you can select '2' in field BroadcastWeekInMonth using a trigger action on document open.

           

          Hope this helps,

          Stefan

            • Re: Capture the 2nd week of every month dynamically
              Melinda Bruck

              Thank you.This works! However, I am having problems with the trigger because the requirement now is to have all months selected in the Month filter listbox every time we are in Week 2 of every month.  For example, when April 8, 2013 arrives (Week 2 of April), I need the months Jan, Feb, March selected in the Month filter box, when May 6th, 2013 (Week 2 of May) arrives I need Jan, Feb, March, April months selected and so on. 

               

              Any suggestions on how to do this? 

               

              Melinda

                • Re: Capture the 2nd week of every month dynamically
                  Stefan Wühl

                  Melinda,

                   

                  create a Select - Select in field action for field Month and a search string like this:

                   

                  =if(only({1<CalendarKey = {'$(=today())'}>} BroadcastWeekInMonth) = 2, '=Month<Month(today())' )

                   

                   

                  First part checks your condition: Is today() in a second broadcast week of a broadcast month, the then branch then returns the actual search string. (I assumed your data model you posted above).

                   

                  See also attached sample.

                   

                  Today, we are in a fifth broadcast week in broadcast month, so you would need to change the 2 to 5 in above for testing.

                   

                  Regards,

                  Stefan