4 Replies Latest reply: Mar 25, 2014 2:05 PM by Massimo Grossi RSS

    Expression in List Box

      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.





        • Re: Expression in List Box
          Alessandro Saccone

          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
            Manish Kachhia

            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

              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
                Massimo Grossi

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


                example, static filter




                load *

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

                where match(year, 2013, 2014);


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




                  rand()*100 as exp1,

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

                AutoGenerate 10000;



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


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

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



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

                Autogenerate vMaxDate - vMinDate ;


                //drop Table MinMaxDate;