Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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
pradeepsagwal
Contributor II
Contributor II

hic

Hi Sir

I am facing problem in finding current fiscal year and current fiscal month. My expression in  chart is like this

sum({1<FYear={'2018/2019'},FMonth={'May'},Project={'ABC} >}Qty)

every month I have to change FMonth and FYear  at start of financial year. I have used your script for fiscal calendar.

I will also use current fiscal month in label.

Thanks in advance.

0 Likes
990 Views
v_guravladimir
Contributor
Contributor

Hi

I so solved this task 

Sum({$<date={">=2018-04-01<2019-03-31"}>}salesum)

0 Likes
968 Views
ChetanPalwe
Contributor II
Contributor II

Hi,

can anyone help me on the below problem.

I am working on periodic calendar(13 periods in each financial year and one period =28 days).

My Fiscal year start from 01 Apr 2017 and ends on 30Mar 2018 also next fiscal year starts from 31 Mar 2018 and ends 29 Mar 2019 and so on …

Currently I am struggling to get the fiscal year for every date. So anyone can suggest me how to retrieve the fiscal year for this calendar.

Please see below output of calendar. Below screenshot is some part of output of calendar  

Periodic_master_cal_output.png

0 Likes
960 Views
Onyi_Nwabia-Robbins
Contributor III
Contributor III

Using Qlik Sense, Novermber 2018 Patch 2

I don't know if this is useful to anyone, but I had a requirement where the startmonth had to be dynamic as the start month was different in different user cases. In this case having the solution in the script wouldn't work. 

Using the example above, I created a master dimension called Contract Month (calculated dimension if in QlikView)

Contract Year 

=dual((Year+ If( Month>=$(vFM), 1, 0)-1) &'/'& ([Display Year] + If( Month>=$(vFM), 1, 0)) ,[Display Year] + If( Month>=$(vFM), 1, 0))

I use Month Year field that is already stored but I sort by expression ascending using:

Mod(Month-$(vFM), 12)+1  

I then create a drop down list that holds the variable $(vFM) and shows the months of the year with their corresponding month value (Jan (1), Feb (2) etc. ) and this control the beginning of the year.

 

0 Likes
947 Views
zahra
Contributor II
Contributor II

Thank you very much.

0 Likes
687 Views
Raju_6952
Creator
Creator

Hi @hic ,

How about week calculation with custom fiscal year script (In my case the fiscal year was calculated from sep1st to august31st) and need to calculate the custom week number starting from sep1st and up to august31st.

 

Thanks in Advance,

Raju

291 Views
CJ_Bauder
Partner - Contributor II
Partner - Contributor II

@Raju_6952 have a look at the WeekStart function. You can also do some mapping functions inthe creation of a calendar to manually assign a week number to each week, just have to be careful with short months (typically handled by a fiscal cal anyways).

Hope this helps!

283 Views
hic
Former Employee
Former Employee

Try

Week(Date+125) as Week,

It will generate a week number where Sep 1 always belongs to week 1.

Also, take a look at https://community.qlik.com/t5/Design/Recipe-for-a-4-4-5-Calendar/ba-p/1464069.

264 Views
Raju_6952
Creator
Creator

Hi @hic ,

by using Week (Date+125) as Week, I am getting duplicated rows with different date range for week start and weekend.

Raju_6952_1-1709115310882.png

Thanks.

 

 

216 Views
hic
Former Employee
Former Employee

You are of course right. I missed the reference day... 

Btw, what day do you use as first day of the week? Monday or Sunday? The two following should work though:

Week(WeekStart(Date+126,0,6),6,0,5) as WeekWithSundayAsWeekStart,
Week(WeekStart(Date+126,0,0),0,0,5) as WeekWithMondayAsWeekStart,

209 Views