3 Replies Latest reply: Jun 6, 2010 7:42 AM by Steve Dark RSS

    time and date

    abdullahkn

       

       

       

       

       

       

       

       



       

       

       

       

       

       

       



       

       

       

       

       

       

       

       





       

       

       

       

       

       





       

       

       

       

       

       

       

       





       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       





      hi all,

      I got this code from developer 1, my question does this code for creating the master calendar is valid for all document or there is some change I have to do. . coz I applied for my own document, but it does'not accept. .

      And if any modification, please tell me in which place. . See my document is having been same like that one but maybe year is different please.

      Help me know.

       

       

       

       

       



       

       

       



       

      varMinDate = Num(Peek('OrderDate', 0,'Orders'))

      ;

       

      LET

       

       



       

       

       

       



       

      varMaxDate = Num(Peek('OrderDate', -1

      ,

       

      'Orders'

       

       



       

      ))

      ;

       

      LET

       

       



       

       

       

       



       

      varToday = num(today())

      ;

       

       

      //*************** Temporary Calendar ***************

      TempCalendar:

      LOAD

      $(varMinDate)

       

       



       

       

       

       



       

       

       

       



       

      - rowno() + 1 AS DateNumber

      ,

       

      date

       

       



       

      ($(varMinDate) - rowno() + 1)

      AS

       

      TempDate

      AUTOGENERATE

      $(varMaxDate)

       

       



       

       

       

       



       

       

       

       



       

      - $(varMinDate) + 1

      ;

       

       

      //*************** Master Calendar ***************

      MasterCalendar:

      LOAD

       

       



       

       

       

       



       

      TempDate AS OrderDate

      ,

       

      Week

       

       



       

      (TempDate) AS Week

      ,

       

      Year

       

       



       

      (TempDate) AS Year

      ,

       

      Month

       

       



       

      (TempDate) AS Month

      ,

       

      Day

       

       



       

      (TempDate) AS Day

      ,

       

      Weekday

       

       



       

      (TempDate) AS WeekDay

      ,

       

      'Q'

       

       



       

      & ceil(month(TempDate) / 3) AS Quarter

      ,

       

      Date

       

       



       

      (monthstart(TempDate), 'MMM-YYYY') AS MonthYear

      ,

       

      Week

       

       



       

      (TempDate)&'-'&Year(TempDate) AS WeekYear

      ,

       

      inyeartodate

       

       



       

      (TempDate, $(varToday), 0) * -1 AS CurYTDFlag

      ,

       

      inyeartodate

       

       



       

      (TempDate, $(varToday), -1) * -1 AS

      LastYTDFlag

       

      RESIDENT

       

       



       

       

       

       



       

       

      TempCalendar

       

      ORDER

       

       



       

       

       

       



       

      BY TempDate

      ASC;

       

      DROP

       

       



       

       

       

       



       

      TABLE

       

      TempCalendar;



       

      regards,

      Abdalla



        • time and date

          Hi

          on your application you use

          let varMinDate = Num(Peek('OrderDate', 0,'Orders'));

           

          do you have OrderDate filed on table call Orders. if not you should replace it with the filed you need to build your calender base on it. :)

            • time and date
              abdullahkn


              Hi,

              I have Order Date field but the thing is that ,,when I execute this code it shows me years not included in my Orders. Or it 's trim years this is thing bro.

              And also what's is the temporary table and why I have to create means what is benefits of it.

              Regards,

              Abdalla

                • time and date
                  Steve Dark

                  Hi there,

                  The point of the temporary table is to get a list of all possible dates between the earliest and latest dates in your data. This will allow you to plot a line graph over time with the time axis being correct (rather than being skewed by missing values).

                  If you do not require the missing dates to have the various permutations worked out for you can just do the various functions directly to the OrderDate field in the Orders table.

                  If you are only wanting dates within a specified range then replace the two peek statements (which are getting max and min) with hard coded date values.

                  Hope that helps.

                  Regards,
                  Steve