topic Filtering table based on date in QlikView

Filtering table based on date

Wed, 15 Oct 2014 13:38:37 GMT

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?

Re: Filtering table based on date

ali_hijazi — Wed, 15 Oct 2014 13:42:37 GMT

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

maleksafa — Wed, 15 Oct 2014 13:55:35 GMT

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

Re: Filtering table based on date

maternmi — Wed, 15 Oct 2014 14:07:49 GMT

Hi,

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

Michael

Re: Filtering table based on date

Wed, 15 Oct 2014 14:21:16 GMT

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 — Wed, 15 Oct 2014 14:56:02 GMT

yes this is done at the script side

Re: Filtering table based on date

ali_hijazi — Wed, 15 Oct 2014 15:01:08 GMT

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);

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

Re: Filtering table based on date

Thu, 16 Oct 2014 06:16:32 GMT

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