topic Removing Years from Listbox in QlikView

Removing Years from Listbox

Mon, 06 Aug 2012 10:04:07 GMT

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.

Re: Removing Years from Listbox

swuehl — Mon, 06 Aug 2012 10:13:44 GMT

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) )

Re: Removing Years from Listbox

rajni_batra — Mon, 06 Aug 2012 10:24:57 GMT

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

Try This Hope this Helps!!!

Re: Removing Years from Listbox

Mon, 06 Aug 2012 11:42:13 GMT

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.

Re: Removing Years from Listbox

nstefaniuk — Mon, 06 Aug 2012 12:43:56 GMT

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