Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing Years from Listbox

Hi,

I am new to qlikview and I am trying to create a list box that only shows certain years. 

In the database where I get my information from (using SQL), I have a field called 'Logdate'.  In my load script I have entered the following to create two new fields called Logdate_year and Logdate_month (which is an exact copy and still show the date as 2012-03-01 00:00:00).

I then created two list boxes, one which has just the year in and the other which has just the month in.  In those list boxes I created the following expressions in the properties -> Field.

ListBox 1    =YEAR(logdate_year)

ListBox 2    =MONTH(logdate_month)

However on my ListBox1 which now just displays the year, I want to only show the last 4 years. 

Currently the following years show in my list box 2012,2011,2010,2009,2008,2007,2006 and 2005 (The oldest date in my Database is 2005-01-01 00:00:00)

I want to be able to show just the following dates, 2012,2011,2010 and 2009.  In addition I want this to change every year, so therefore when we start adding 2013 to the database, I want it to show 2013,2012,2011,2010.

4 Replies
swuehl
MVP
MVP

I would suggest to create the year and month fields in the load script, but in your list box, it could probably look like

=if( Year(logdate_year) > year(today())-4, year(logdate_year) )

edit:

Or if you want to check against the max date in your database instead today():

=if( Year(logdate_year) > max(total year(logdate_year))-4, year(logdate_year) )

rajni_batra
Specialist
Specialist

=if(Year> Year(Today())-4,Year)

Try This Hope this Helps!!!

Not applicable
Author

Hi Swuehl,

I took your advice and sorted the data in the load script.  I changed the date to a number, removed the amount of days I wanted to removed as a number and then converted back into a date.  It has worked brilliantly.

nstefaniuk
Creator III
Creator III

Hello.

If I have understood the issue and the solution, take care that when you will select a Year in your listbox, you won't select the Year but the the values of Logdate_year that matches with the result of the expression.

It means that when you think selecting 1 value in Year, in fact you will select 365 dates. You can have issues in advanced scripting, and other tricky issues.

I suggest to:

- calculate a field Year in script

- calculate a field Month in script

- If you want only the 4 last years, reduce your data to keep only the 4 last years

- If you want to keep all your data but display only the 4 last years in listbox, you can link fact table to Year table only for the 4 last years, or link the fact table to the Year table for all rows and set to null values that are less than the 4 last years :

FACT_TABLE

...

id_year

YEAR_TABLE

id_year

value_year : set to null if < Year(Today()) - 3