10 Replies Latest reply: Dec 16, 2016 6:22 PM by Marco Wedel RSS

    Date format in number

    ANDREINA TOLEDO

      Hello,

        This is a easy question:

      This is an extract of my MasterCalendar:

      MasterCalendar:

      Load

      TempDate As CalendarDate,

      MonthEnd(TempDate) As ReportingDate,

      week(TempDate) As CalendarWeek,

      WeekDay(TempDate) as CalendarWeekDay,

      weekstart(TempDate) As CalendarWeekStart,

      Year(TempDate) As CalendarYear,

      Month(TempDate) As CalendarMonth,

      MonthName(TempDate) As CalendarMonthYear,

      Day(TempDate) As CalendarDay,

      'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,

       

      Thank you!

        • Re: Date format in number
          ANDREINA TOLEDO

          Sorry  I've forgotten to  post the  question is : Why do I get the ReportingDate as a number and the Others as Dates?

           

          Thank you!

          • Re: Date format in number
            Prashant Sangle

            and your question is????

             

            Regards

            • Re: Date format in number
              Shraddha Gajare

              date(TempDate) As CalendarDate,

              • Re: Date format in number
                ANDREINA TOLEDO

                Yes, but Why the other date fields were not converted?

                 

                Even I indicate ReportingDate as a date(ReportingDate) the engine insists in indicate it as a "Number" date format.

                  • Re: Date format in number
                    ANDREINA TOLEDO

                    This is the hole code:

                    Load

                    TempDate As CalendarDate,

                    date(MonthEnd(TempDate)) As ReportingDate,

                    week(TempDate) As CalendarWeek,

                    WeekDay(TempDate) as CalendarWeekDay,

                    weekstart(TempDate) As CalendarWeekStart,

                    Year(TempDate) As CalendarYear,

                    Month(TempDate) As CalendarMonth,

                    MonthName(TempDate) As CalendarMonthYear,

                    Day(TempDate) As CalendarDay,

                    'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,

                    Num(Month(TempDate)) as CalendarPeriod,

                    IF(TempDate>=addmonths(Date(Today()-Day(Today())+1),-12),1,0) as Rolling12MonthsFlag,

                    if(InYearToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar YTD TY],  // All Dates to Date this Year

                    if(InYearToDate (TempDate, weekstart(today())-1, -1),1,0) as [Calendar YTD PY],  // All Dates to Date Last Year

                    if(InWeekToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar WTD TY], // All Dates to Date this Week this Year

                    if(InWeekToDate (TempDate, weekstart(today()-7)-1, 0),1,0) as [Calendar Last WTD TY], // All Dates to Date Last Week this Year

                    if(InMonthToDate(TempDate, (today())-1, 0),1,0) as [Calendar MTD TY], // All Dates to Date this Year

                    if(InYear (TempDate, weekstart(today())-1, -1),1,0) as [Calendar FY PY], // All Dates Last Year

                    if(InYear (TempDate, weekstart(today())-1, 0),1,0) as [Calendar FY TY] // All Dates This Year

                    ;

                     

                     

                    //=== Generate a temp table of dates ===

                    LOAD

                    date(mindate + IterNo()) AS TempDate

                    ,maxdate // Used in InYearToDate() above, but not kept

                    WHILE mindate + IterNo() <= maxdate;

                     

                     

                    //=== Get min/max dates from Field ===/

                    LOAD

                    Num('$(vCalendar_Start_Date)') + IterNo() as mindate,

                    Num(MonthEnd(Today())) as maxdate

                    AutoGenerate 1

                    While IterNo() <= $(vCalendar_Number_Days_Between_Dates);

                     

                    I have attached the snapshot of the table as is loaded (just the Reporting Date is in numbers)Capture.PNG

                      • Re: Date format in number
                        Anil Babu Samineni

                        Check this part, I've changed from num to simple date. Now you can check whether it willl or won't

                         

                        //=== Get min/max dates from Field ===/

                        LOAD

                        Date('$(vCalendar_Start_Date)') + IterNo() as mindate,

                        Date(MonthEnd(Today())) as maxdate

                        AutoGenerate 1

                        While IterNo() <= $(vCalendar_Number_Days_Between_Dates);

                    • Re: Date format in number
                      Marco Wedel

                      Hi,

                       

                      I guess you might have changed the document standard format of the ReportingDate field.


                      Check in Settings/Document Properties/Number tab.


                      hope this helps


                      regards


                      Marco