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 · Accepted solution]

Hi,

to code your search expression dynamicly (e.g. in your OnOpen-Trigger) try the today()-function instead of hardcoding the year. Similar to this in the search string:

= year(today())

Regards, Roland

[Miguel_Angel_Baeyens]

Hello,

First note that OnOpen macros may not work when the document is opened by client (either Desktop, Plugin, AJAX).

If that's not the case, then set a variable in your script

SET vCY = Year(Today());

Then in your action, set "Select in Field", "Search String" to

=$(vCY)

Try with a button object, it should work.

Hope this helps.

[#4]

Hi Kunle,

Thanks for the reply. But it seems that am going wrong somewhere..its not working out as expected.

As you can see, I am going to OnOpen-->Select in field and in field, I am giving the year-column name and in search string am giving the present year expression =Year(today()) .

Then I am giving OK and coming out. Saving the report. closing it. opening it fresh, expecting that I'll get a report with a default selection on open with year as 2011.

Can you kindly try this out and let me know...it would be really greatful.

[#5]

This is exactly what I've done and it works fine. The selection seems to work regardless if the year (YEAR_NUM in your case) is loaded as text or numeric.

[#6]

@Miguel

...i tried the way you told, and its working...

So if I want the default selection to be current year and current month, can I create another variable

SET vCM = Month(Today());

and in the OnOpen actions, as a second option I give in select field.

=$(vCM)

Is it the most easy way or is there any better options to have current year and month selected by default when the report opens?

[Miguel_Angel_Baeyens]

Hi,

The option Roland told you is good enough, you will need to create a different action for the current month. Basically, both answers do the same, but his saves you one step (creating the variable in the script).

If you use a master calendar, you can use a composite field monthyear, for example

Date(Today(), 'YYYYMM') AS YearMonth

And then do a selection in the action likewise.

Hope that helps

[#8]

Thanks Miguel,

Due to some prob, the option that Ronald suggested is not working,

But when ever I set a variable in the script side, pass it to the report and use it for default settings..(as the one you have suggested) it is working perfectly.

Now I am stuck up with a small issue. I am setting a new variable in the script side..

SET vCYM = Date(Today(),'YYYYMM');

With this, I will be able to get the YearMonth combination like (for eg 201102) in the report side. I want to set the default selection as the YearMonth combination with one count lesser. That is..this month if I reload the script, the report on open should show the default selection as 201101 . I tried giving =$((vCYM)-1) in the OnOpen Select Field, but it is not working..when I checked this yields some junk value. Any solution for this?

[Miguel_Angel_Baeyens]

Hello,

I'd do all conversions in the script side, so

SET vCYM = Date(AddMonths(Today(), -1),'YYYYMM'); //For today, this will return 201101

Hope that helps.

[#10]

Thanks Miguel,

That was perfect..