topic AW:How to get the last year selected on report open, by default in QlikView

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

Mon, 07 Feb 2011 15:36:34 GMT

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)

AW:How to get the last year selected on report open, by default

Mon, 07 Feb 2011 15:52:59 GMT

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

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

Miguel_Angel_Baeyens — Mon, 07 Feb 2011 15:54:25 GMT

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.

AW:How to get the last year selected on report open, by default

Mon, 07 Feb 2011 16:05:00 GMT

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.

AW:How to get the last year selected on report open, by default

Mon, 07 Feb 2011 19:48:27 GMT

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.

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 09:52:52 GMT

@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?

AW:How to get the last year selected on report open, by default

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 10:01:57 GMT

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

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 11:24:35 GMT

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?

AW:How to get the last year selected on report open, by default

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 11:30:44 GMT

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.

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 12:03:02 GMT

Thanks Miguel,

That was perfect..

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 13:33:26 GMT

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?

AW:How to get the last year selected on report open, by default

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 13:44:39 GMT

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.

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 15:24:59 GMT

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

AW:How to get the last year selected on report open, by default

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 15:36:57 GMT

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.

AW:How to get the last year selected on report open, by default

Tue, 08 Feb 2011 15:57:53 GMT

Perfect..

Thanks a lot Miguel for the help..

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

Wed, 06 Jul 2011 15:14:18 GMT

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

Thanks

vas