topic Re: Expression in List Box in QlikView

Expression in List Box

Tue, 25 Mar 2014 16:39:49 GMT

Hi All,

I have an autogenerated calendar which is producing Month & Year. It has years from 2012 to 2015. My data only has values for 2013 & 2014. I would like to produce a list box with only 2013 & 2014 in it. At the moment, it shows all 4 years, how do I filter out the years with no data. I also have a date field which I can refer to.

Thanks

Herbert

Re: Expression in List Box

alexandros17 — Tue, 25 Mar 2014 16:47:16 GMT

you can set an expression instead of a field in the list box so you can write:

if(year=2013 or year=2014, year, null())

in this way list box will show only 2013 and 2014 year

Re: Expression in List Box

MK_QSL — Tue, 25 Mar 2014 17:33:11 GMT

Instead of selection Year directly in List BOX, type below in List Box Expression...

=IF(Year < 2015 and Year > 2012, Year)

Re: Expression in List Box

Tue, 25 Mar 2014 17:56:26 GMT

i would suggest you to filter calendar records in your script based on existing year values in fact table. that way you dont have to manipulate everytime on the frondend.

Re: Expression in List Box

maxgro — Tue, 25 Mar 2014 18:05:09 GMT

if you only need 2 years why not filter the calendar?

example, static filter

Calendar:

noconcatenate

load *

from Calendar.qvd (qvd) // your original calendar

where match(year, 2013, 2014);

other example, filter based on min max date of fact table

Fact:

LOAD

rand()*100 as exp1,

date(floor(makedate(2013) + rand()*731)) as date

AutoGenerate 10000;

MinMaxDate:

LOAD min(date) as MinDate, max(date) as MaxDate Resident Fact;

Let vMinDate = Peek('MinDate', -1, 'MinMaxDate') ;

Let vMaxDate = Peek('MaxDate', -1, 'MinMaxDate') ;

Calendar:

Load Date(recno()+$(vMinDate)-1) as Date1

Autogenerate vMaxDate - vMinDate ;

//drop Table MinMaxDate;