8 Replies Latest reply: Nov 5, 2014 11:40 AM by Ali Hijazi RSS

    how to Get month and Qtr information

      Hi Team,

       

      I have a date field in YYYYMMDD format like 20140919.

      Using the Left function i can get the year field. How to get the month field and quarter field.

      My fist month should be November the last month should be October.

       

      thanks a lot for your help.

       

      Regards,

      Viresh

        • Re: how to Get month and Qtr information
          Manish Kachhia

          Month(Date(DATE#(YourDateField,'YYYYMMDD'))) as Month

          Year(Date(DATE#(YourDateField,'YYYYMMDD'))) as Year

          'Q'& Ceil(Month(Date(DATE#(YourDateField,'YYYYMMDD')))/3) as Quarter

          • Re: how to Get month and Qtr information
            anbu cheliyan

            Month(Date#(Date_Field,'YYYYMMDD')) -- Month

            Ceil(Month(Date#(Date_Field,'YYYYMMDD'))/3) -- Quarter


            Mid('20140919',5,2) -- Month

            Ceil(Mid('20140919',5,2)/3) -- Qtr

            • Re: how to Get month and Qtr information
              Anand Chouhan

              Try with

               

              Load

              Month(Date(Date#(Mid(Datefield,5,2),'mm'),'mm') ) as Month

              'Q' & Ceil (Month(Date(Date#(Mid(Datefield,5,2),'mm'),'mm') ) /3) as Quarter

              From Source;

               

              Regards

              Anand

              • Re: how to Get month and Qtr information
                Ali Hijazi

                here is the code of a full calendar

                StartYear = Year(Today()) -4;
                YearsToGenerate=Year(Today()) - $(StartYear) +1;
                UNQUALIFY *;
                //Look through number of years defined above
                for i = 1 to YearsToGenerate;
                let curYear = StartYear+(i-1);
                let StartDate = makedate(curYear);  //Returns first day of year
                let YearEnd = YearEnd(StartDate);   //Returns last day of year
                let EndWeek = WeekEnd(StartDate);
                let DayEndWeek = day(EndWeek);
                let WeekStart = date(EndWeek);
                //use if week starts on Saturday let WeekStart = date(EndWeek - 1);
                //Calculate number of days in the year
                let TotalDays =(YearEnd-StartDate)+2;
                //Create a temporary calendar
                TempCalendar:
                load recno()*$(i) as DateKey,
                '$(StartDate)'+recno()-1 as PERIOD_DATE
                autogenerate(TotalDays-1);
                   
                Next
                //Building the master calendar with most date dimensions
                MasterCalendar:
                load
                num(daystart(PERIOD_DATE)) as PERIOD_DATE,
                Week(PERIOD_DATE) as Week,
                DateKey,
                Year(PERIOD_DATE) as Year,
                Month(PERIOD_DATE) as Month,
                Num(Month(PERIOD_DATE)) as MonthNum,
                Day(PERIOD_DATE) as Day,
                'Q' & Ceil(Month(PERIOD_DATE)/3) as Quarter,
                Year(PERIOD_DATE) & '-' & 'Q' & Ceil(Month(PERIOD_DATE)/3) as QUARTER_YEAR,
                Year(AddMonths(PERIOD_DATE,-3)) & '-' & 'Q' & ceil(Month(AddMonths(PERIOD_DATE,-3))/3) AS PREVIOUS_QUARTER_YEAR,
                Year(PERIOD_DATE) & ' - S' & Ceil(Month(PERIOD_DATE)/6) as Season,
                if(MOD(Month(PERIOD_DATE),6) =0,1,0) AS IS_LAST_MONTH_OF_SEASON,
                Month(PERIOD_DATE) & '-' & Day(PERIOD_DATE) as MonthDay,
                Date(MonthStart(PERIOD_DATE), 'MMM-YYYY') as MonthYear,
                Week(PERIOD_DATE) as CalendarWeek,
                Week(PERIOD_DATE) & '-' & Year(PERIOD_DATE) as WeekYear,
                WeekDay(PERIOD_DATE) as WeekDay
                resident TempCalendar  order by PERIOD_DATE Asc;
                Drop table TempCalendar;
                  • Re: how to Get month and Qtr information

                    Hey Ali,

                     

                    This is really nice. How to make Nov as my first month instead of Jan.

                     

                    Regards,

                    Viresh

                      • Re: how to Get month and Qtr information
                        Ali Hijazi

                        SET vFiscalYearStartMonth = 4;//you need to update this value to suit your needs

                        LET vStartDate = Num(YearStart(Today(), -1));

                        LET vEndDate = Num(YearEnd(Today()));

                         

                        FiscalCalendar:

                        LOAD

                        *,

                        Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

                        Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

                        LOAD

                        *,

                        Year(Date) AS Year, // Standard Calendar Year

                        Month(Date) AS Month, // Standard Calendar Month

                        Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

                        Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

                        Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

                        YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

                        LOAD

                        Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

                        RangeSum(Peek('RowNum'), 1) AS RowNum

                        AutoGenerate vEndDate - vStartDate + 1;