Expressing Years in Quarters

[#1]

Hi There,

I'm using the following expression to express months as quarters:

'Q' & Ceil(Month(Date(Date# (<MyDate> , 'YYYYMM'),'DD-MMM-YY'))/3) as Quarter

However, where I work, our Financial year begins in November, so Q1 is (Nov, Dec, Jan).

The expression above returns Q1 as the first three month of the calendar year (Jan, Feb, Mar).. Any tips on how I can modify this to return Q1 starting in Nov?

Thanks

John

[tresesco]

Try something like:

If((Month-10)>0,Ceil((Month-10)/3),Ceil((Month-10+12)/3)) as QuarterNumber

Modify accordingly.

[Brice-SACCUCCI]

Hi,

here's a solution without if statement, the first load is just here to generate some example data.

tmp:

LOAD

    Month(MakeDate(2013, RowNo())) AS Month

AutoGenerate

    12;

LOAD

    Month,

    'Q' & (mod(Ceil((Month - 2) / 3), 4) + 1) as Quarter

RESIDENT

    tmp;

Regards,

Brice

[Josh_Good]

You may also want to check out the post below on creating a master calendar:

Re: Creating A Master Calendar

[#5]

Hi All,

Thanks for the great feedback - I'm starting to find my way about a bit better

While Tresesco and Brices' code worked there are is one flaw - the first two months of Q1 are shown in the same year as Q2 , Q3 and Q4. For example, for FY2013, I need Q1 to be Nov 2012, Dec 2012 and Jan 2013. You solutions give me Nov 2013, Dec 2013 and Jan 2013. While looking at this I realised that I also need to change my year to Financial year, so that FY2013 runs from Nov 2012 to Oct 2013. Can you help with that please?

I'm trawling through the links on creating a Master Calendar but am still unsure on how to set this up - all help appreciated

Cheers

john

[Josh_Good · Accepted solution]

Hi John,

You can use the AddMonth function to 'shift' the date from the calendar date to the fiscal date.  In your case, you need to move the date ahead 2 months (so Nov 1st, 2012) is shifted to the first day of the fiscal year.

Load

....

Year(addMonths(MyDate,2)) as FiscalYear

....

From

....

[Clever_Anjos]

try

'Q' & Ceil(Month(addmonths(Date# (<MyDate> , 'YYYYMM'),3))/3) as Quarter

[jemancilla]

You can make a master calendar using Brice Saccucci code and adding "FiscalYear":

//----- Example data:

tmp:

LOAD

    MakeDate(2013, RowNo()) AS Date

AutoGenerate

    12;

//----- Creating a master calendar

Calendar:

Load

  FieldValue('Date',iterno()) As Date

  ,Year(FieldValue('Date',iterno())) As Year

  ,Year(FieldValue('Date',iterno()))+Ceil(Month(FieldValue('Date',iterno()))/10)-1 As FiscalYear

  , 'Q' & (mod(Ceil((Month(FieldValue('Date',iterno())) - 2) / 3), 4) + 1) as Quarter   //Brice's code

AutoGenerate 1

While(Len(FieldValue('Date',Iterno())));

//----- Good bye example data!

Drop table tmp;

[#9]

try this

quartername(mydate) as QuarterYear.

[tresesco]

For year, you may try like:

Load

        'FY' & If(Month=11 or Month=12, Year+1, Year) as CalculatedYear