topic Re: Financial Year vs Calendar in QlikView

Financial Year vs Calendar

Wed, 20 Feb 2013 16:01:23 GMT

I would like to be able to give my users a facility to display their data for financial years rather than calendar. My financial year is Aug-Jul. I have a datestamp field which I extract Year() and Month() from. I have two list boxes with months and years but what I would like to have in the Year box is my financial years i.e. [2010-11], [2011-12] etc. How can I achieve this? Your help would be greatly appreciated.

Re: Financial Year vs Calendar

hic — Wed, 20 Feb 2013 16:57:02 GMT

You should create this in a Master Calendar. See example below.

All you need is some kind of Transaction table with a field called "date". The sample creates Years with values like 2010/2011 and months with standard month names - but sorted correctly.

HIC

Set vFirstMonthOfFiscalYear = 4;

MasterCalendar:

Load distinct *,

if(Month>FiscalMonth,Year & '/' & (Year+1), (Year-1) & '/' & Year) as FiscalYear;

Load *,

Dual(Month,Mod(Month-$(vFirstMonthOfFiscalYear),12)) as FiscalMonth;

Load

date,

Year(date) as Year,

Month(date) as Month,

Date(MonthStart(date),'YYYY MMM') as RollingMonth

resident Transactions;

Re: Financial Year vs Calendar

lawrenceiow — Mon, 20 Apr 2015 11:08:17 GMT

Excellent, just what I needed, thank you.

I especially like the idea of using Dual so that the months are sorted correctly.