Fiscal Year

[hic]

A common situation in Business Intelligence is that an organization uses a financial year (fiscal year) different from the calendar year. Which fiscal year to use, varies between businesses and countries. [Wikipedia] But how would you solve that in QlikView?

[hic]

A fiscal year other than the calendar year implies a number of additional requirements in the QlikView app: The most obvious is that the year used in all charts and reports must correspond to the fiscal year which runs over a different set of dates than the calendar year.

Further, the notation sometimes changes: You probably want to display years as ‘2012/2013’ instead of just the year number.

Also, other fields, e.g. Month and Week must be assigned to the fiscal year as well as the calendar year.

Finally, the sort order of field values changes in some cases. E.g. you want to sort the months using the first financial month as the first month: [Apr..Mar] instead of [Jan..Dec]. (Compare with the month order in the graph and the list box.)

There is a very simple way to achieve this in QlikView:  Just add the necessary fields in the master calendar and use these in all situations where you need a calendar field. There are many ways that this can be done, but my suggested solution is the following:

1. Create a variable that contains the month number of the first month of the fiscal year. Assuming that April is the first month of your fiscal year, this variable should get the value ‘4’.
   
   
2. Create numeric values of the necessary fields. Usually the number of the fiscal year is defined by its end, so (again using April as the first month) April 2013 belongs to the fiscal year ‘2014’.
   
   
3. Create dual values of the necessary fields.

The script for creating fiscal year and fiscal month then becomes:

Set vFM = 4 ;                                                          // 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
          *;
Load Year(Date)                              as Year,           // Your standard master calendar
         Month(Date)                            as Month,
        …

Other fields, like week, day, etc. can also be created in a similar way.

A comment on the field naming: In this script I use lowercase ‘f’ as prefix for the numeric values and uppercase ‘F’ for the dual values. In real life you may want to just have the dual fields (no numeric duplicates) and name these differently, e.g. just ‘Year’ and ‘Month’. If you do, you must also rename the original calendar year and calendar month accordingly.

The bottom line is anyway that you can solve this problem just by adding a couple of lines in you master calendar. No set analysis is needed. And no complex chart expressions are needed.

Simplicity.

HIC

[#3]

Many thanks. Iis very usefull  

[#4]

How can I make the start-month of the fiscal year dynamic? Meaning, the user can choose whether they want it the 7th, 10th, 4th or whatever month of the year? I tried using an INPUTFIELD, but can't get it to work.

[hic]

You'd need to have this in a variable and expose it in an input box. Then you can create calculated dimensions similar to the ones above.

Alternatively you could have the twelve months in a logical island (an unconnected field) that has "Always one Selected value" set. And then use this in the calculated dimensions.

However, be aware that all calculated dimensions have a cost: It takes CPU-time to calculate them and the more of such features you have, the slower QlikView will get.

Use your CPU-power wisely. Don't waste it on "unnecessary" features.

HIC

[#6]

Thanks for clear and simplified solution...

I am new to qlikview and was not knowing about fiscal calendar concept..

Thanks for the clear and concise description.

[MK_QSL]

Dear Henric,

Can you let me know how to add FWeek and FQuarter ?

[#8]

Here's how I created some fiscal periods recently.  Because I had some null values in my dates, I was getting 'Q' only with no number, so I used a Rob Wunderlich suggestion to only create my calendar if the len() of the Month was greater than zero.  Otherwise, the below also ordered my FYQ and FQFY

SET vFM=2;    //First month of fiscal year

Calendar:

LOAD

    Dual(if(len(FQuarter)>0, FQuarter&'-'&fYear),             QuarterName(link_date,0,2))        AS FQFY,    //  FiscalQuarter-FiscalYear

    Dual(if(len(FQuarter)>0, 'FY'&right(fYear, 2)&FQuarter), QuarterName(link_date,0,2))    AS FYQ,        // FiscalYearQuarter

    *

    ;

LOAD

    Dual(fYear-1 &'/'& fYear, fYear)                                             AS FYear,   // Dual fiscal year

    Dual(Month, fMonth)                                                          AS FMonth,    // Dual fiscal month

    Dual(if(len(fMonth/3)>0, 'Q'&ceil(fMonth/3)), QuarterName(link_date,0,2))    AS FQuarter,// Fiscal Quarter

    *

    ;

LOAD

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

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

    *

    ;

LOAD Distinct

    link_date,

    Year(link_date)                                                                AS Year,    // Your standard master calendar

    if(len(link_date)>0, 'Q'&ceil(Month(link_date)/3))                            AS Quarter,    // Your standard calendar Quarter

    Month(link_date)                                                            AS Month    // Your standard calendar Month

Resident Link_Table;

[#9]

Hi Henric,

I read over your example above and have a question.  I am trying to create a fiscal year calendar and here is what I have done so far:

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

Then I have my Standard Master calendar that looks like this:

MasterCalendar:

//     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

RESIDENT Work_Order;   

My question is you mentioned above that other fields like Week, Qtr, etc will need to be setup.  Are you talking adding that information to the load statements above.  If so Can you help me out with this.  If you want me to post this somewhere else I can.

David

[hic]

I would do the following:

Set vFM = 6 ;    // First month of fiscal year
Set vFD = 6;     // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:
Load distinct
   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year
   Dual(Month, fMonth)                as FMonth,  // Dual fiscal month
   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
   Ceil(([Request Date]-StartOfFWeekOne+1)/7) as FWeekNo,
   *;
Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1            as fMonth,  // Numeric fiscal month
   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,
   *;
Load
   [Request Date],
   YearStart([Request Date],0,$(vFM)) as FYearStart,
   Year([Request Date])               as Year,
   Month([Request Date])              as Month,
   Date(Monthstart([Request Date]), 'MMM-YYYY') as MonthYear,
   Week([Request Date])               as ISOWeekNo,
   Dual(WeekDay([Request Date]),Mod(WeekDay([Request Date]-$(vFD)),7)+1) as WeekDay,
   Day([Request Date])                as Day,
   Date([Request Date], 'MM/DD')      as DATEMMDD
Resident Work_Order;   

The Quarter is straightforward, but the weeks are trickier... Note also that I changed your WeekDay definition.

See more on

http://community.qlik.com/blogs/qlikviewdesignblog/2014/01/21/week-start

http://community.qlik.com/blogs/qlikviewdesignblog/2014/01/27/week-numbers

HIC

[#11]

Thank you so much.  It worked perfectly and now I understand what you did.

David

[#12]

Thanks Henric. Nice post.

Btw. how to find current quarter i.e as per today, it is Q1. So should we use today() and your fMonth.

All they need is Month wise sales for current quarter with their fiscal calendar.