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.

       

      Thanks

       

      Herbert

        • 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

                 

                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;