Removing Years from Listbox

[#1]

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.

[swuehl]

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]

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

Try This Hope this Helps!!!

[#4]

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]

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