topic Re: Search year based on expression in QlikView

Search year based on expression

Mon, 04 Jul 2011 13:47:54 GMT

Hello community,

I have a question about selecting a year based on todays date (QlikView v. 9 SR6).

Here's the scenario: I have list box displaying a field named date_YYYY, containing the values 2008, 2009, 2010 and 2011.

I would like to be able to highlight the year belonging to the date of exactly one month ago from today. To do this, I try to use an expression in the search box:

=Year(Addmonths(today(),-1))

However, this selects all years instead of 1.

Using the exact same expression as Text in a text object displays the year just fine, in case of today that's 2011.

I' ve tried an alternative approach using dollar sign expansion and a variable containing the expression, but no results so far.

Does anybody have an idea how to get this to work?

Thanks,

Robert

Re: Search year based on expression

john_duffy — Mon, 04 Jul 2011 14:30:55 GMT

Hi Rob.

I am using QV 8.5. To accomplish this I use a macro using an expression similar to yours. See the attached example which highlights the year based on the previous month. In this example the year is highlighted on open and post reload.

Let me know if this helps.

John.

Search year based on expression

Mon, 04 Jul 2011 15:04:04 GMT

John,

Thanks for your answer. I've tried the same thing and it works, but unfortunately I'm gonna need a solution that does not depend on a macro (company policy) -- sorry, should have mentioned this in my question.

- Robert

Re: Search year based on expression

john_duffy — Mon, 04 Jul 2011 15:35:45 GMT

Hi Rob.

I found this link from Rob Wunderlich on a discussion regarding setting initial selections - http://qlikviewnotes.blogspot.com/2010/02/setting-initial-selections-in-version-9.html.

Using the information in this link I was able to set the YYYY field using an on open and on post reload trigger (see attached). We are in the process of migrating from QV8.5 to QV10 so I was able to do this in QV10 (see attached). Not sure if it works the same in QV9.

John.

Search year based on expression

Tue, 05 Jul 2011 06:45:47 GMT

Thanks John,

That does it.

By the way, I found another way for entering a search value in the list box that does work too: instead of entering the expression =Year(Addmonths(today(),-1)),one should enter =date_YYYY=Year(Addmonths(today(),-1)).

Thanks again,

- Robert