Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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.
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
hi,
try year(today()) in search string
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?