Expression in List Box

[#1]

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

[alexandros17]

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

[MK_QSL]

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

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

[#4]

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.

[maxgro]

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;