Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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;

0 Replies