7 Replies Latest reply: Oct 16, 2014 2:16 AM by Divvya Ramesh RSS

    Filtering table based on date


      I have a table box with two columns. Below are the first few rows of the table box.

      01 Sep 2014Task 1`
      02 Sep 2014Task 2
      03 Sep 2014

      Now I want a list box that has the following four options to select from:

      • Last One Week
      • Last Two Weeks
      • Last Three Weeks
      • Last Four weeks

      When I select any one of the  four options I want the table to display the data accordingly.

      Example : If user selects Last One Week, then I want the table to display the data only for the last one week (current week).. I don't want any older data to appear in the table.

      How can I make this work?

        • Re: Filtering table based on date
          Ali Hijazi

          I would add a new field as follows:

          if Today() - DateField <=7, 'Last Week',if(Today()-DateField <=14 and >7, '2 Weeks',..... as Week Indicator

          then you can add your list box listing values for Week Indicator field


          hope this helps

          • Re: Filtering table based on date
            Malek Safa

            there is a very nice blog on how to dynamically select time frames, it would be interesting to have a look.



            • Re: Filtering table based on date
              Michael Matern



              I think Ali suggest to do it in the script, don't you Ali?




              • Re: Filtering table based on date

                What if my Last one week is not defined as 'the last 7 days' but defined by the Week Number. For Example- If the current week is W18 then my 'last one week' will be defined as W17 and my 'last two weeks' will be defined by W17 & W16 and so on..

                  • Re: Filtering table based on date
                    Ali Hijazi

                    then you need to create a master calendar and specify the week number in it the master calendar would be something as follows:

                    StartYear = Year(Today()) -4;

                      YearsToGenerate=Year(Today()) - $(StartYear) +1;


                      UNQUALIFY *;

                      //Look through number of years defined above

                      for i = 1 to YearsToGenerate;



                      let curYear = StartYear+(i-1);

                      let StartDate = makedate(curYear);  //Returns first day of year

                      let YearEnd = YearEnd(StartDate);   //Returns last day of year

                      let EndWeek = WeekEnd(StartDate);

                      let DayEndWeek = day(EndWeek);


                      let WeekStart = date(EndWeek);

                      //use if week starts on Saturday let WeekStart = date(EndWeek - 1);


                      //Calculate number of days in the year

                      let TotalDays =(YearEnd-StartDate)+2;



                      //Create a temporary calendar


                      load recno()*$(i) as DateKey,

                      '$(StartDate)'+recno()-1 as PERIOD_DATE





                      //Building the master calendar with most date dimensions




                      num(daystart(PERIOD_DATE)) as PERIOD_DATE,

                      Week(PERIOD_DATE) as Week,


                      Year(PERIOD_DATE) as Year,

                      Month(PERIOD_DATE) as Month,

                      Num(Month(PERIOD_DATE)) as MonthNum,

                      Day(PERIOD_DATE) as Day,

                      'Q' & Ceil(Month(PERIOD_DATE)/3) as Quarter,

                      Year(PERIOD_DATE) & '-' & 'Q' & Ceil(Month(PERIOD_DATE)/3) as QUARTER_YEAR,

                      Year(AddMonths(PERIOD_DATE,-3)) & '-' & 'Q' & ceil(Month(AddMonths(PERIOD_DATE,-3))/3) AS PREVIOUS_QUARTER_YEAR,

                      Year(PERIOD_DATE) & ' - S' & Ceil(Month(PERIOD_DATE)/6) as Season,

                      if(MOD(Month(PERIOD_DATE),6) =0,1,0) AS IS_LAST_MONTH_OF_SEASON,

                      Month(PERIOD_DATE) & '-' & Day(PERIOD_DATE) as MonthDay,

                      Date(MonthStart(PERIOD_DATE), 'MM  Week(PERIOD_DATE) as CalendarWeek,


                    M-YYYY') as MonthYear,

                      Week(PERIOD_DATE) & '-' & Year(PERIOD_DATE) as WeekYear,

                      WeekDay(PERIOD_DATE) as WeekDay

                      resident TempCalendar  order by PERIOD_DATE Asc;


                      Drop table TempCalendar;



                    The calendar Week will be the week that you want