Discussion Board for collaboration related to Creating Analytics for QlikView.
I have a table box with two columns. Below are the first few rows of the table box.
Now I want a list box that has the following four options to select from:
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?
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
there is a very nice blog on how to dynamically select time frames, it would be interesting to have a look.
I think Ali suggest to do it in the script, don't you Ali?
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..
yes this is done at the script side
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;
//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
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