Fiscal Year Question

[#1]

I have the following information in my load statement:

MasterCalendar:

LOAD DISTINCT

       [Request Date],

       date([Request Date]) AS DATE,

       //AddYears(date([Request Date]),1) AS [Request 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

[#2]

Hello David,

Did you check the solution proposed under:

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

[Anonymous]

Hi,

Below script may help you to create fiscal year.

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

Thanks

Satya

[#4 · Accepted solution]

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.