2 Replies Latest reply: Aug 30, 2017 11:24 AM by Mathias Vieweg RSS

    Help: Filter pane for time periods

    Mathias Vieweg

      Hello community,

      I would like to create a filter pane that includes the following time period entries so that th data is shown for the selected period:

       

      E.g.

      last 12 month

      last 9 month

      last 6 month

      last 12 weeks

      last 8 weeks

      last 4 weeks

      last 14 days

      how can I create this ?

       

      Thanks in advance,

      Mathias

        • Re: Help: Filter pane for time periods
          Tim Driller

          Hello Mathias,

           

          you can use this script as MasterCalender

           

          //Generate calendar range based on the Claendar start and end variables. All date within the range are genrated.
          //usually you would use your min/max from desired Table Dates here
          LET vCalendarStart = Date('2015-01-01'); // Static start
          LET vCalendarEnd = Date(today()); // Today

          LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

          Calendar:
          LOAD
          *,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -7))  and Date <= Date('$(vCalendarEnd)'), 1,0) as Last6Months,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -10)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last9Months,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -13)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last12Months,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -4))  and Date <= Date('$(vCalendarEnd)'), 1,0) as Last12Weeks,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -3))  and Date <= Date('$(vCalendarEnd)'), 1,0) as Last8Weeks,
          If(Date > Date(MonthStart('$(vCalendarEnd)', -2))  and Date <= Date('$(vCalendarEnd)'), 1,0) as Last4Weeks,
          If(Date > Date('$(vCalendarEnd)'-14) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last14Days
          ;
          LOAD
          *,
          Num(Ceil(Month/3), 'Q0') AS Quarter,
          WeekDay(Date) AS WeekDay,
          Date(MonthStart(Date), 'YYYY MMM') AS YearMonth,
          WeekName(Date) AS YearWeek,
          Date#(Date(Date, 'MMMM'), 'MMMM') AS MonthName  //Month according to MonthLongNames definition
          ;
          LOAD
          Date,
          Year(Date) AS Year,
          Num(Month(Date), '00') AS Month,
          Num(Day(Date), '00') AS Day,
          Num(Week(Date), '00') AS Week
          ;
          LOAD
          Date($(#vCalendarStart) + RecNo()-1) AS Date 
          AutoGenerate $(#vCalendarLength);

          See Attachment for QVW

           

          Regards

          Tim