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

[hic]

You should define flags in your master calendar. See for instance Year-over-Year Comparisons.

For your specific case - current fiscal quarter - you could use

   If(QuarterStart(Date,0,$(vFM))=QuarterStart(Today(),0,$(vFM)),1,0) as IsCurrentQuarter

HIC

[#4]

Thnks a ton sir for sharing this important concept

[Anonymous]

Thanks for sharing.

You always make life easy

We are implementing quite similar thing.

[#6]

Hi Henric

i am facing a doubt in a fiscal year. when i am reloading the following script it is giving error

'<Tempdate>' field not found>

for the line

<Ceil(([TempDate]-StartOfFWeekOne+1)/7) as FWeekNo,>

Plz Explain

SET vFM=4;

SET vFD=6;

MasterCalendar:

Load 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(([TempDate]-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

  //TempDate as IslandDate,

  TempDate AS Date,

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

  Week(TempDate) AS Week,

  Day(TempDate) AS Day,

  Month(TempDate) AS Month,

  Year(TempDate) AS Year,

  //if(len(TempDate)>0,Date(YearStart(TempDate,0,4),'MMM-YYYY')&'-'&Date(YearEnd(TempDate,0,4),'MMM-YYYY')) as FiscalYear,

  Weekday(TempDate) AS WeekDay,

  'Q' & ceil(month(TempDate) / 3) AS Quarter,

  Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

  inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[hic]

That is simple. You don't load TempDate in the bottom load... You have renamed it to "Date" so that is what you should use in the upper Loads.

HIC

PS ... and why do use a Load Resident from a temporary table? Just put a preceding load in front of your initial table. It's faster and you don't need to drop anything.

[#8]

Thanks Henric

and please tell me how the load takes place from top to bottom or from bottom to top

Plz explain

[hic]

The first Load (top Load) loads from the result of the 2nd Load, which in turn loads from the result of the 3rd Load, etc. So, from a time perspective, the bottom Load is executed first.

Read more in Preceding Load.

HIC

[#10]

Hi, I followed this tuto but this script load only the academic year of the first (*.xslx) file. (CanonicalDate, FileName, *, *, *  are loaded from all files)

Can you help me please? (Urgent!!)

Load  

Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual academic year  

Dual(Month, fMonth)              as FMonth, // Dual academic month

    *;

Load  

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

Mod(Month-$(vFM), 12)+1        as fMonth, // Numeric academic month

    *;

load

Month(CanonicalDate) as Month,

Year(CanonicalDate) as Year,

   *;

Load          // Your standard master calendar       

FileName() as FileName,

   *,

[Date 1er contact] as CanonicalDate,

'1er contact' as DateType,

   *,

   *,

   *,

   FROM C:\Prosp\*.xlsx (ooxml, embedded labels);

// I separated the loading dates to put all in CanonicalDate

Load [Date Action 1] as CanonicalDate,

'Action 1' as DateType

FROM C:\Prosp\*.xlsx (ooxml, embedded labels);

Load

[Date Action 2] as CanonicalDate,

'Action 2' as DateType

FROM C:\Prosp\*.xlsx (ooxml, embedded labels);

Load

[Date Action 3] as CanonicalDate,

'Action 3' as DateType

FROM C:\Prosp\*.xlsx (ooxml, embedded labels);

Load

[Date Action 4] as CanonicalDate,

'Action 4' as DateType

FROM C:\Prosp\*.xlsx (ooxml, embedded labels);

[hic]

The top part of the script is your master calendar for the canonical date. This table should have one record per date, so...

• Add a distinct clause to the first Load
• Remove the "FileName" field
• Remove the "DateType" field

The second part of the script is the date bridge for the canonical date. It should have one record per combination of date and Key to the data (OrderLineID in my example). So...

• Add the Key field

Then it should work.

A third change that I would make, is to change the order of the two parts, so that the master calendar table is created through a

   Load ... Resident DateBridge.

HIC

[#12]

thank you very much but now i have another problem. I created a list of selection of the academic year but when I select a field, my charts will not change