How to limit what is displayed in a YEAR list box

[#1]

My first post

I have searched before posting, but I am a real rookie with Qlikview, so forgive me if I have not searched well enough.  It is probably because I do not know the terminology yet.

We have transactions in our system going forward to 2015 due to accruals, but I do not want users to see or be able to select any YEAR into the future.

How can I write an expression in a list box to only display years up until current year (2012)?

Current YEAR selection:

2007  2008  2009  2010  2011  2012  2013  2014  2015

I wish the list box only to display:

2007  2008  2009  2010  2011  2012

Regards

Thor

[#2]

Hi Thor,

use an expression within your listbox.

properties --> Choose the last entry "<expression>" from the pulldown-menu of field.

Edit it like this (MyYear is my data-field):

=if (MyYear<=Year(today()), MyYear)

Regards, Roland

[Miguel_Angel_Baeyens]

Hi Thor,

Roland's suggestion will work fine. But I'd recommend you to create a new field in your script (name it YearShow or something different that the Year field name) where you set that condition. The idea is to avoid conditional expressions that will work just fine with a few thousand records, but may slow your charts and your document if it handles several hundreds of thousands of rows.

Again, credit to Roland, because that will work.

Miguel

[#4]

Hi Miguel,

yes, that may be true. There is even another reason: when you create alot of such listboxes it is more comfortable. .

But I think for the first post an expression like above will do the job for a while.

Are you im Miami this year again?

RR

[#5]

Thanks for the suggestion. 

As I said, I am really new at this, so as embarrasing as it sounds, I cannot figure out how to do this in the edit Expressions to make it work.

I do have around 1 million rows, so maybe I should try Miguels version?  I have created new fields before.  E.g. year(transactiondate) etc., but how do I set a condition on that field to make this work?

Once again, I really appreciate your help.  I have only tried Qlikview now for a couple days, and it is so much fun

Best regards

Thor

[#6 · Accepted solution]

OK Thor,

I really don't know why I checked my emails at this late hour (quarter to midnight), but now I can anwser your question in the sense of Miguel:

Try to generate a new field like the following code snippet (in the load script):

LOAD

. . .

year(transactiondate)           AS TransYear, // this is what you know right now

if (year(transactiondate) >= Year(Today()), year(transactiondate))    AS SelectYear,   // new field

. . .

FROM

and use the new Field "SelectYear" in the according Listbox (and whereever else usefull).

HtH

Roland

[christophebrault]

Hi,

if you want don't to use further data at all in your aplication, you can use WHERE in your script.

After your FROM and before the final ";", write where(year<=year(today)

This avoid loading data after 2012.

[#8]

Thank you for taking the time, even at late hours Roland

Great solution, and it worked perfectly.  I just changed > to <.

year(transactiondate)           AS TransYear, // this is what you know right now

if (year(transactiondate) >= Year(Today()), year(transactiondate))    AS SelectYear,   // new field

Now, a quick question which is probably very easy for you 🙂

I tried using an OnActivateSheet Action to Select Curent Year and Current Month, but I have not been successful at doing it.

I would think all I have to do is Settings - Sheet Properties - Triggers - OnActivate Sheet - And then add:

Select in Field:  Field = YEAR and Search String = year(today)

But it does not work

/Thor

[christophebrault]

hi,

try year(today()) in search string

[#10]

Doesn't work.

Is there anything I need to activate Triggers?  I have tried Select Possible as well on Field Month, but that does not work either.

Any ideas?