Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Search year based on expression

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Search year based on expression

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.

4 Replies
Partner
Partner

Re: Search year based on expression

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.

Not applicable

Search year based on expression


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

Partner
Partner

Re: Search year based on expression

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.

Not applicable

Search year based on expression

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