3 Replies Latest reply: Jun 24, 2014 7:14 AM by David Young

# Fiscal Year Question

I have the following information in my load statement:

MasterCalendar:

[Request Date],

date([Request Date]) AS DATE,

//     Dates

year([Request Date]) AS Year,

month([Request Date]) AS Month,

date(monthstart([Request Date]), 'MMM-YYYY') AS MonthYear,

'Q'&Ceil(Num(Month([Request Date])/3)) as Quarter,

week([Request Date]) AS Week,

weekday([Request Date] + 2) AS Weekday,

day([Request Date]) AS Day,

date([Request Date], 'MM/DD') AS DATEMMDD

My question is this works based on Calendar Year.  I want to base it on Fiscal Year.  My guess I can add a line above the MasterCalendar like:

 Set vFM = 6 ; // First month of fiscal year

I am a little lost what the next steps are.  I have read over a ton of examples but still not sure.  Can someone help me out.

David

• ###### Re: Fiscal Year Question

Hello David,

Did you check the solution proposed under:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year

• ###### Re: Fiscal Year Question

Hi Jean-Pierre -

I have read over the link you provided.  I have not been successful with the whole Master Calendar idea and have not used it much. I have updated my Master Calendar Tab in the load statement to the following:

Set vFM = 6 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

*;

Load Year + If(Month>=\$(vFM), 1, 0) as fYear,           // Numeric fiscal year

Mod(Month-\$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

//     Dates

year([Request Date]) AS Year,

month([Request Date]) AS Month,

date(monthstart([Request Date]), 'MMM-YYYY') AS MonthYear,

'Q'&Ceil(Num(Month([Request Date])/3)) as Quarter,

week([Request Date]) AS Week,

weekday([Request Date] + 2) AS Weekday,

day([Request Date]) AS Day,

date([Request Date], 'MM/DD') AS DATEMMDD

The article mentions doing the same or Quarter, Week, etc.  This where I am still confused.  Any help would be great.

• ###### Re: Fiscal Year Question

Hi,

Left(YearName([Request Date], 0,6),4) as FiscalYear

Thanks

Satya