Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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?

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

71 Comments
Not applicable

Simple Solution..

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

Now it look simple..

Thanks Henric

18,903 Views
christophebrault
Specialist
Specialist

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

18,903 Views
hic
Former Employee
Former 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

18,903 Views
christophebrault
Specialist
Specialist

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
18,903 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
18,903 Views
hic
Former Employee
Former 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

18,903 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.

15,736 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
15,736 Views
hic
Former Employee
Former 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

15,736 Views
Not applicable

Thanks,

Francesco

0 Likes
15,736 Views