2 Replies Latest reply: Mar 9, 2017 12:01 PM by Cathy Duvall RSS

    Calendar fields returning 0 when selecting. Do I need to floor my Date field as it has DateTime values?

    John Blomqvist

      Hi all,

       

      I am getting 0's when selecting values in fields such as Year, Month etc.

       

      My Date field in the Facts table is a Date and Time field and contains date and time values.

       

      Do I need to floor it?

       

      I am using a standard master calendar script like this:

       

      MinMax:

      LOAD

          Min(Date) AS MinDate,

          Max(Date) AS MaxDate

      RESIDENT Facts;

       

       

      // ***** Set up the variables *****   

      LET vMinDate = Num(PEEK('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(PEEK('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

       

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

      TempCal:

      LOAD

          DATE($(vMinDate) + ROWNO()-1) AS TempDate

      AUTOGENERATE

          $(vMaxDate) - $(vMinDate) +1;

       

      DROP TABLE MinMax;

       

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

      MasterCalendar:

      LOAD

          TempDate AS Date,

          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, $(vToday), 0) * -1 AS CurYTDFlag,

          INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

          MonthStart(TempDate) as MonthStart

       

       

      RESIDENT TempCal

      ORDER BY TempDate ASC;

      DROP TABLE TempCal;

        • Re: Calendar fields returning 0 when selecting. Do I need to floor my Date field as it has DateTime values?
          Sunny Talwar

          I think that will be a good idea

           

          Fact:

          LOAD Date as TimeStamp,

                    Date(Floor(Date)) as Date,

                    .....

          FROM ....;

           

          MinMax:

          LOAD Min(Date) AS MinDate,

              Max(Date) AS MaxDate

          RESIDENT Facts;

           

          // ***** Set up the variables ***** 

          LET vMinDate = Num(PEEK('MinDate', 0, 'MinMax'));

          LET vMaxDate = Num(PEEK('MaxDate', 0, 'MinMax'));

          LET vToday = $(vMaxDate);

           

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

          TempCal:

          LOAD

              DATE($(vMinDate) + ROWNO()-1) AS TempDate

          AUTOGENERATE

              $(vMaxDate) - $(vMinDate) +1;

           

          DROP TABLE MinMax;

           

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

          MasterCalendar:

          LOAD

              TempDate AS Date,

              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, $(vToday), 0) * -1 AS CurYTDFlag,

              INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

              MonthStart(TempDate) as MonthStart

          RESIDENT TempCal

          ORDER BY TempDate ASC;

          DROP TABLE TempCal;

          • Re: Calendar fields returning 0 when selecting. Do I need to floor my Date field as it has DateTime values?
            Cathy Duvall

            If flooring the DateTime does not work, you may need to change the number in the UI.  Before using the date in your application, right click on an empty spot on your sheet, click properties and select and date field.  After the date field is selected, Right click on the top caption and list box properties.  Override document settings, to change the number format to date with the needed Format Pattern that is already in your system.  See below: