Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Fiscal Year Question

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

1 Solution

Accepted Solutions
Not applicable

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. 



3 Replies
Not applicable

Re: Fiscal Year Question

Hello David,

Did you check the solution proposed under:

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

satyadev_j
Valued Contributor

Re: Fiscal Year Question

Hi,

Below script may help you to create fiscal year.

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

Thanks

Satya

Not applicable

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. 



Community Browser