10 Replies Latest reply: Oct 8, 2013 6:41 AM by mayilvahanan ramasamy RSS

    Predefine Date range

      Dear Community,

       

      May I know how to define date range in the script?

       

      Requirement:

      Used Dimension =

      Week1 - Week6,

      Week2 - Week7,

      Week3 - Week8,

      ...

      Expression =

      Sum(Sales)

       

      The first bar in the chart will show sum of sales from week1-week6,

      2nd bar will show sum of sales from week2-week7,

      3rd bar will show sum of sales from week3-week8...

       

      I tried with this script :

      'W' & Ceil(week(TempDate)/6) As ROIWeek

      but it is not working as desired, it gives me 6 weeks which is week1-week6, week7-week12, week13-week18...

      Not suit the requirement.

       

      The master calendar code is as shown below:

      ...
      MasterCalendar:
      LOAD TempDate AS [Date],
           Date(TempDate,'DD/MM/YY') as ShortDate,
           //week(TempDate) AS Week,
           year(TempDate) AS Year,
           month(TempDate) AS Month,
           if(Month(TempDate)='Jan',1,
             if(Month(TempDate)='Feb',2,
             if(Month(TempDate)='Mar',3,
             if(Month(TempDate)='Apr',4,
             if(Month(TempDate)='May',5,
             if(Month(TempDate)='Jun',6,
             if(Month(TempDate)='Jul',7,
             if(Month(TempDate)='Aug',8,
             if(Month(TempDate)='Sep',9,
             if(Month(TempDate)='Oct',10,
             if(Month(TempDate)='Nov',11,
             12
             ))))))))))) as Month_Number,
           day(TempDate) AS Day,
           weekday(TempDate) AS WeekDay,
           'Q' & Ceil(Month(TempDate)/3) AS Quarter,
           'Q' & Ceil(Month(TempDate)/3)&'-'&right(year(TempDate),2) AS QuarterYear,
           date(monthstart(TempDate), 'MMM YY') AS MonthYear,
           //week(TempDate)&'-'&right(Year(TempDate),2) AS WeekYear,
           Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CueYTDFlag,
             Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag
      RESIDENT TempCalendar
      ORDER BY TempDate Asc;
      DROP Table TempCalendar,Temp;

       

      Thanks and best regards,

      Chanel

        • Re: Predefine Date range
          Gysbert Wassenaar

          For that you'll need another table that links the week with every range it is part of. You'll create the week field in the master calendar and then add another table with the range and week number. That would something like this:

           

          Weekranges:

          load * inline [

          Range, Week

          Week1 - Week6, 1

          Week1 - Week6, 2

          ...etc

          Week1 - Week6, 6

          Week2 - Week7, 2

          Week2 - Week7, 3

          ...etc

          Week2 - Week7, 7

          ..etc

          ];


          Note, the inline load is just an example. You can create the table any way you want, for example from loading an excel file.

            • Re: Predefine Date range

              Thanks for prompt response!

              But it's kinda impossible for us to update the .qvw script every week.

               

              Initially i think of :

              'W' & Ceil(week(TempDate)/6) As ROIWeek

              But it's not working according to requirement, it gives week1-6, 7-12...

               

              Any idea like while loop or for loop that will automatically create a date range?

               

              Best regards,

              Chanel