Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
"'FY' & Year(Reporting_Date) & ' Q' &" - Hre Year returns the Calendar Year , but not the Fscal Year. So this will not work for me.
Load the fiscal year as an inline table
Sorry !
Got your question wrong.
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;