Fiscal Calendar

[#1]

Hi

I have the following code for generating the Fiscal Calendar.

This is working fine for me.

But for the Fiscal Qaurter, i need the format of FY&Quarter to be shown, Like FY13 Q1 or 2013 Q1 something like that.

Can you please help me?

Set vFirstMonthOfFiscalYear = 11;

Load distinct *,
if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;

Load *,

          Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
   
Load

          Reporting_Date,

          Year(Reporting_Date) as CYear,

          Month(Reporting_Date) as CMonth,

          week(Reporting_Date) as CWeek,

          Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

          'Q'& ceil(Month(Reporting_Date)/3) as CQuarter,

'Q'&

If(Match(Month(Reporting_Date),'Nov','Dec','Jan') > 0 , 1,

If(Match(Month(Reporting_Date),'Feb','Mar','Apr') > 0 , 2,

If(Match(Month(Reporting_Date),'May','Jun','Jul') > 0 , 3,

If(Match(Month(Reporting_Date),'Aug','Sep','Oct') > 0 , 4)))) as FiscalQuarter


resident Volume;

[Brice-SACCUCCI]

Hi,

can you try the following?

Set vFirstMonthOfFiscalYear = 11;

Load distinct *,
if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;
Load *,

          Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
   
Load

          Reporting_Date,

          Year(Reporting_Date) as CYear,

          Month(Reporting_Date) as CMonth,

          week(Reporting_Date) as CWeek,

          Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

          'Q'& ceil(Month(Reporting_Date)/3) as CQuarter,

'FY' & Year(Reporting_Date) & ' Q' &

If(Match(Month(Reporting_Date),'Nov','Dec','Jan') > 0 , 1,

If(Match(Month(Reporting_Date),'Feb','Mar','Apr') > 0 , 2,

If(Match(Month(Reporting_Date),'May','Jun','Jul') > 0 , 3,

If(Match(Month(Reporting_Date),'Aug','Sep','Oct') > 0 , 4)))) as FiscalQuarter

Regards,

Brice

[#3]

"'FY' & Year(Reporting_Date) & ' Q' &" - Hre Year returns the Calendar Year , but not the Fscal Year. So this will not work for me.

[#4]

Load the fiscal year as an inline table

[#5]

Sorry !
Got your question wrong.

[Brice-SACCUCCI]

Oops sorry!

Please look at this post : Fiscal Calendar

Regards,

Brice

[#7]

Hi,

Try This

Set vFirstMonthOfFiscalYear = 11;

Load distinct *,
if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;

Load *,

          Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
   
Load

          Reporting_Date,

          Year(Reporting_Date) as CYear,

          Month(Reporting_Date) as CMonth,

          week(Reporting_Date) as CWeek,

          Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

          'Q'& ceil(Month(Reporting_Date)/3) as CQuarter,

         Year(Reporting_Date) &' '& 'Q'& ceil(Month(Reporting_Date)/3) as FiscalYearQuarter

resident Volume;