Capture the 2nd week of every month dynamically

[#1]

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? 

[swuehl]

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

[#3]

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

[swuehl]

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