Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Capture the 2nd week of every month dynamically

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? 

3 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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