Filtering table based on date

[#1]

Hi

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

Date	Details
01 Sep 2014	Task 1`
02 Sep 2014	Task 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?

[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

[maleksafa]

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

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/06/dynamically-selecting-timeframes

[maternmi]

Hi,

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

BR

Michael

[#5]

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..

[ali_hijazi]

yes this is done at the script side

[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

  TempCalendar:

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

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

  autogenerate(TotalDays-1);

  Next

  //Building the master calendar with most date dimensions

  MasterCalendar:

  load

  num(daystart(PERIOD_DATE)) as PERIOD_DATE,

  Week(PERIOD_DATE) as Week,

  DateKey,

  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

[#8]

What if I want to create a calendar with only two fields Date Key & Date Label. That has 7 dates corresponding to Label 'Last One Week' and 14 dates corresponding to 'Last Two Weeks' and so on