How to get the current year selected on report open, by default

[#1]

Hi,

I am using QlikView 9 and I am facing an issue which seems very basic, but unfortunately I am not able to find the solution.My report has a list box for year (which displays last 4 years, including the current year). I want the report to show a default selection of the current year on report open --that is (making it clear !) on open, the report should show a default selection with year selected as the current year. I have tried two things that works fine for this..but it involves me going and hardcoding the current year selection, which is not accepatable.

1) I went to Settings-->Document Properties-->(Document Event Triggers)OnOpen-->Edit Action -->Selection-->Select in field-->for 'field', I give the year field name ..and for search string i give 2011

2) I create a book mark with the year selected as 2011. Then i saved it. Settings-->Document Properties-->(Document Event Triggers)OnOpen-->Edit Action-->Bookmarks-->Apply Bookmark --> and i give this saved bookmarks' ID.

So, please help me to get a solution for this so that on report load I get a default selection with current year coming up (and the current year should be dynamic..that is the next year, i needn't go and manually change it to 2012)

[#2]

Again, .. we had a change in our requirement.

We cannot be always sure that our warehouse tables will be having data upto the previous month. By any chance if the warehouse do not have the previous month data, setting the previous month as default month will show 'no data' charts on load and it's not good.

We have another option..our fact table has a MONTH_SID column which has values in YearMonth format (eg 201102). This will have the latest MONTH_SID of the last load. So it'll be fine if I could assign to a variable, the max value of MONTH_SID from the fact table.

But my question is, in script editor, we do this this variable setting/assigning first and then only we start calling the select queries from various tables. I want to assign to a variable (say vLASTLOAD_YM), the max value of the column MONTH_SID from the fact table named IC_F_RECRUIT .. can you kindly help me with this?

[Miguel_Angel_Baeyens]

Hello,

Something like the following would do

MaxMonthSID:LOAD Max(MONTH_SID) AS MAX_MONTH_SIDRESIDENT IC_F_RECRUIT; // If the table IC_F_RECRUIT already exists, then resident, otherwise, datasource LET vMaxMonthSID = Peek('MAX_MONTH_SID', 0); // This assigns the first value of the field to the variable//LET vMaxMonthSID = FieldValue('MAX_MONTH_SID', 1); // If the above doesn't work, uncomment this DROP TABLE MaxMonthSID;

Hope that helps.

[#4]

Thanks Miguel,

now vMaxMonthSID is having the latest MONTH_SID. Is there a way by which I can split the content of this variable and assign it to two new variables for year and month.

That is, say for example if vMaxMonthSID has value as 200902 ..i'd like to create two new variables in the script side one (say vLY) to hold the year value as 2009 and another (say vLM) to hold the month value.

I am trying to use substring function to extract out these but getting stuck..

[Miguel_Angel_Baeyens]

Hi,

Yep, something like the following will work:

LET vMY = Date(Today(), 'YYYYMM');LET vCY = Left($(vMY), 4);LET vCM = Right($(vMY), 2);

Hope that helps.

[#6]

Perfect..

Thanks a lot Miguel for the help..

[#7]

Hello can any one help me how to get the selected bookmark name form bookmark object in a variable.

Thanks

vas