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

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

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

Hi Manish,

Thanks for the reply. Its working fine but my first quarter should consists of Nov , Dec and Jan instead of normal quarter Jan, Feb and Mar.

Is this possible?

Regards,

Viresh

• Re: how to Get month and Qtr information

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

Try with

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

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

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:

*,

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

*,

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

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

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

AutoGenerate vEndDate - vStartDate + 1;