0 Replies Latest reply: Jul 24, 2012 6:05 AM by Matt Cayford RSS

    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;