Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Fiscal Year

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]

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.

Bar chart.png

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.)

List boxes.png

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,
        …

Table.png

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

61 Comments
Not applicable

Simple Solution..

Usually we are writing as a single expression but on complicated way..

Now it look simple..

Thanks Henric

443 Views
christophebraul
Contributor III

Hi,

Thanks for this usefull solution. I think it works very well in most situation.

But in my case, i often use set analysis for YTD, MTD or Year v Previous year. For example, i use the field "Year" in the set expression. I think this won't work when user select values in FYear...

443 Views
Employee
Employee

For YTD calculations you indeed need Set Analysis. But I would do the Set Analysis on FMonth and FYear instead of on the standard calendar year and month.

HIC

443 Views
christophebraul
Contributor III

Yes, sorry i wasn't clear enought in my previous post. In my case, users want to switch between fiscal and calendar year. And i don't see any solution instead of creating an if condition...

0 Likes
443 Views
Not applicable

We use a different way to calculate the fiscal year.  vLM is the last month of the fiscal year:

     AddMonths(Today(),-vLM)

This calculation works equally well in QlikView and SQL, using GetDate() instead of Today().

The month calculation can be similar:

     Num(Month(AddMonths(Today(),-vLM)))

I cannot remember where I first saw this, but it has been very useful for us.

0 Likes
443 Views
Employee
Employee

There’s more than one way to skin a cat...

The main point is that these fields should be created in the data model, since this simplifies user interaction, chart creation and all formulae. And this, you obviously have realized long ago.

HIC

443 Views
Not applicable

That's right.

We could make better use of Dual though. We have not really incorporated it into our models, but it does look very effective.

Thanks.

443 Views
Not applicable

Hi Henric,

      Probably I miss something but how is formatted/populated RMonth?  Be patient with me since I'm new in the community.

Thanks,

Francesco

0 Likes
443 Views
Employee
Employee

The best way to create a field for rolling month is, in my mind the following

Date(MonthStart(Date),'YYYY-MMM') as RMonth

This way you get a date serial number (=correct sort order) corresponding to the first day of the month, but formatted without the day information.

HIC

443 Views
Not applicable

Thanks,

Francesco

0 Likes
443 Views
Not applicable

Many thanks. Iis very usefull  

0 Likes
443 Views
Not applicable

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.

0 Likes
443 Views
Employee
Employee

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

0 Likes
443 Views
Not applicable

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.

0 Likes
443 Views

Dear Henric,

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

0 Likes
443 Views
Not applicable

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;

443 Views
Not applicable

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

0 Likes
443 Views
Employee
Employee

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

443 Views
Not applicable

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

David

0 Likes
443 Views
Not applicable

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.

0 Likes
443 Views
Employee
Employee

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

443 Views
Not applicable

Thnks a ton sir for sharing this important concept

0 Likes
443 Views
kirankkk
Contributor

Thanks for sharing.

You always make life easy

We are implementing quite similar thing.

0 Likes
443 Views
Not applicable

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;

0 Likes
443 Views
Employee
Employee

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.

443 Views
Not applicable

Thanks Henric

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

Plz explain

0 Likes
443 Views
Employee
Employee

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

0 Likes
443 Views
Not applicable

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);

0 Likes
443 Views
Employee
Employee

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

0 Likes
443 Views
Not applicable

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

0 Likes
443 Views