topic Re: Fiscal Calendar in QlikView

Fiscal Calendar

Mon, 07 Oct 2013 08:42:28 GMT

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;

Re: Fiscal Calendar

Brice-SACCUCCI — Mon, 07 Oct 2013 08:54:19 GMT

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

Re: Fiscal Calendar

Mon, 07 Oct 2013 10:56:27 GMT

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

Re: Fiscal Calendar

Mon, 07 Oct 2013 10:58:21 GMT

Load the fiscal year as an inline table

Re: Fiscal Calendar

Mon, 07 Oct 2013 10:59:27 GMT

Sorry !
Got your question wrong.

Re: Fiscal Calendar

Brice-SACCUCCI — Mon, 07 Oct 2013 12:26:57 GMT

Oops sorry!

Please look at this post : Fiscal Calendar

Regards,

Brice

Re: Fiscal Calendar

Mon, 07 Oct 2013 12:44:59 GMT

Hi,

Try This

Set vFirstMonthOfFiscalYear = 11;

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

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

resident Volume;