Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Filtering on current month and preceding 12 months

Before I throw this PC out the window to be shortly followed by myself, can I please ask for some advice.

I currently have a requirement for 2 pie charts - one to show number of Ref in the current month as well as one showing the number of Ref in the preceding 12 months.

As it stands, I have set up a calendar/slider object which can only show the total number of Ref ever. (count Ref)

Is there an easy way of creating these 2 seperate views (current month/previous 12 months) by altering/adding to the script below? (which was created by a consultant whom I have no access to)

The key field that I wish to manipulate is Link_Date (format 41114 being today) which is what I wish to filter on calendar month and previous 12 months.

Btw,  I am unable to post code as I am working on a terminal server and much to my frustration, I cannot open any example QVW due to the restriction of my local Qlikview program(Personal Edition).




//Calendar Start Date
LET vDateMin = Num(MakeDate(2011,09,05));
//Calendar End Date
//LET vDateMax = Num(MakeDate(2012,12,31));//Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateMax = Num(weekstart(now())-1);//Floor(YearEnd(AddMonths(Today(), 12)));


LET vDateToday = Num(Today());
LET vDateLastWeek = Num(weekstart(Today())-1);


trace $(vDateLastWeek);

TempCalendar:
LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
floor(TempDate) as Link_Date,
TempDate AS mCalendarDate,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,



dual(text(date(weekend(TempDate),'DD-MMM-YY')),
     if(month(weekend(TempDate))=1 and week(weekend(TempDate))=51,1,week(weekend(TempDate)))) as Nice_Week,
if(TempDate>(now()-(7)) and TempDate<(now()+1),'Previous Week') as myselector ,

TempDate as dateto,
TempDate-7 as Last_Week,


year(weekend(TempDate)) as dateto_Year,

month(TempDate) as dateto_Month,
week(weekend(TempDate)) as dateto_Week,

if(($(vDateLastWeek)-TempDate)<0,dual('Future',-1),
if(ceil(($(vDateLastWeek)-TempDate+1)/7)=1,
      dual('Current Week',0),
          dual('Week-'&num(ceil(($(vDateLastWeek)-TempDate+1)/7)-1,'#0'),ceil(($(vDateLastWeek)-TempDate+1)/7)-1))) as Week_Class,

num(if(($(vDateLastWeek)-TempDate)<0,dual('Future',-1),
if(ceil(($(vDateLastWeek)-TempDate+1)/7)=1,
      dual('Current Week',0),
          dual('Week-'&num(ceil(($(vDateLastWeek)-TempDate+1)/7)-1,'#0'),ceil(($(vDateLastWeek)-TempDate+1)/7)-1)))) as Week_ClassNum



RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());


LET vMondayofLastWeek=num(weekend($(vDateLastWeek)))-6;
LET vSundayofLastWeek=num(weekend($(vDateLastWeek)));
LET vMondayofThisWeek=num(weekend($(vDateLastWeek)))+1;
LET vTuesdayofThisWeek=num(weekend($(vDateLastWeek)))+2;

Community Browser