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

[marcel_olmo]

Hi Henric Cronström,

I have an issue with two beginning months of fiscal years (one for each period). How would you solve it?

It's explained here :

Dual fiscalmonth challenge

Regards, Marcel

[blunckc1]

Hi hic‌,

I am new to Qlik and am stumbling why way through things.  I tried to follow your script but I am getting a "field not found - <Year> script error and I can't work out why.  Can you help please?

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

//Load pay period data

FiscalCalendar:

LOAD Dual([Fiscal Year]-1 &'/'& [Fiscal Year], fYear) as FiscalYear,

     Dual(Month([End Date]), fMonth) as [Fiscal Month],

     *;

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

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

     *;

PayPeriodCalendar:

Load [Start Date],

     [End Date],

     [Pay Date],

     [Pay Period #],

     date(MakeDate([Fiscal Year],Month([End Date]),1), 'MMM-YYYY') AS [Fiscal Month Year],

     Date([Start Date]+iterno()-1) as ReferenceDate

FROM

MappingTables\PayPeriodCalendar.xls

(biff, embedded labels, table is Sheet1$) While [Start Date]+IterNo()-1 <=[End Date];

Thank you so much if you can!

Regards

Carl

[hic]

Well, "Year" doesn't exist. You never load or define a field "Year", so the error message is understandable. One possible solution is that you replace your "Year" references with Year(ReferenceDate).

HIC

[blunckc1]

Yeah I worked out my problem, I didn't understand the preceding load aspect at first. Thanks for taking the time to reply. Really appreciate your time.

Cheers

Carl Blunck

[Anonymous]

Hi Henric,

I have this as my master calendar,

MinMax:
LOAD
Max([A Awardered Date]) as MaxDate,
Min([A Awardered Date]) as MinDate
RESIDENT Fact;

LET varMinDate = num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
LET vToday = $(vMaxDate);

Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Set vFM = 4 ;                                                          // First month of financial year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
     Dual(Month, fMonth)              as FMonth,            // Dual financial month
          *;
Load Year + If(Month>=$(vFM), 1, 0)  as fYear,         // Numeric financial year,
         Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric financial month
          *;
Load Year(Datefield)                  as Year,           // Your standard master calendar
         Month(Datefield)             as Month,

Datefield
Resident Datefield;
DROP Table Datefield;

What happen is that my record are duplicated and different fiscal year.

As shown comparing with FYear and without. I match it with different colour for easier comparison. What would have cause this and how to I fix it?

Regards,

Frederic

[hic]

For some reason you have two values of FYear for a single date. I cannot understand why since I don't have the app or the data model so I suggest you open a separate thread for this question, and post a document that shows this problem.

HIC

[kusterere]

Hi! Thank you so much or posting this script and the replies on the comments. I have edited my script so it is as follows:

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((MCDate-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

   MCDate,

   YearStart(MCDate,0,$(vFM)) as FYearStart,

   Year(MCDate)               as Year,

   Month(MCDate)              as Month,

   Date(Monthstart(MCDate), 'MMM-YYYY') as MonthYear,

   Week(MCDate)               as ISOWeekNo,

   Dual(WeekDay(MCDate),Mod(WeekDay(MCDate-$(vFD)),7)+1) as WeekDay,

   Day(MCDate)                as Day,

   Date(MCDate, 'MM/DD')      as DATEMMDD

Resident FY1617Records;

However when I select a year and a month it generates results for two months in one of my tables.

Should I be using a different field for the month display across the top? I am currently using FMonth

[#10]

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

//Calender for DATE_COMPLETED

Temp: 

Load 

               min(DATE_COMPLETED) as minDate, 

               max(DATE_COMPLETED) as maxDate 

Resident V_QLIK_OPS_WR; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

DateCompletedCalendar: 

Load 

               TempDate AS DATE_COMPLETED, 

               week(TempDate) As CompletedWeek, 

               Year(TempDate) As CompletedYear, 

               Month(TempDate) As CompletedMonth, 

               Day(TempDate) As CompletedDay, 

               YeartoDate(TempDate)*-1 as CompletedCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as CompletedLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as CompletedRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as CompletedMonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as CompletedQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CompletedWeekYear, 

               WeekDay(TempDate) as CompletedWeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

This is my Master Calendar script. Where would I put the above code in my script?

[santiago_respane]

Great and simple solution!

Thanks a lot!

[gileswalker]

Hi Henric,

I am needing some assistance in a related topic, yet slightly different.  I would be really grateful to hear any recommendations to my situation:

Dealing with dynamic reporting periods across multiple customers

Regards

Giles