Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;