topic Re: Fiscal Year Question in QlikView

Fiscal Year Question

Mon, 23 Jun 2014 22:23:38 GMT

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

Re: Fiscal Year Question

Tue, 24 Jun 2014 01:16:43 GMT

Hello David,

Did you check the solution proposed under:

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

Re: Fiscal Year Question

Anonymous — Tue, 24 Jun 2014 01:52:24 GMT

Hi,

Below script may help you to create fiscal year.

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

Thanks

Satya

Re: Fiscal Year Question

Tue, 24 Jun 2014 11:14:13 GMT

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.