Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

New Contributor III

How to change the Fiscal Year ?


I found the script as described below which creates a fiscal year calendar. The fiscal year starts on September and ends on August the 31st of each year. e.g : from 09/01/2016 to 08/31/2017, the fiscal year is called FY17.

Having said that, from September 1st, 2017 :

  - from 09/01/2017 to 12/31/2017, the fiscal year must be called FY17B

  - for the next years, the fiscal year starts on January and ends on December 31st

  1. e.g : the fiscal year FY18 starts on January 1st, 2018 and ands on Decemebr 31st, 2018.

How to adapt/modify the script below please ?

Many thanks,


SET vFiscalYearStartMonthSales = 9;

LET vStartDateSales = Num(YearStart(Today(), -6));

LET vEndDateSales = Num(YearEnd(Today(),1));




Dual('Q' & Ceil(FiscalMonthSales/3), Ceil(FiscalMonthSales/3)) AS FiscalQuarterSales, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date_Sales), 'MMM')), FiscalMonthSales) AS FiscalMonthNameSales; // Fiscal Calendar Month Name



Year(Date_Sales) AS YearSales, // Standard Calendar Year

Month(Date_Sales) AS MonthSales, // Standard Calendar Month

Date(MonthEnd(Date_Sales), 'MMM') AS MonthNameSales,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date_Sales)/3), Ceil(Month(Date_Sales)/3)) AS QuarterSales,  // Standard Calendar Quarter

Mod(Month(Date_Sales) - $(vFiscalYearStartMonthSales), 12)+1 AS FiscalMonthSales,  // Fiscal Calendar Month

'FY' & right(YearName(Date_Sales, 0, $(vFiscalYearStartMonthSales)),2) AS FiscalYearSales; // Fiscal Calendar Year


Date($(vStartDateSales) + RangeSum(Peek('RowNumSales'), 1) - 1) AS Date_Sales,

RangeSum(Peek('RowNumSales'), 1) AS RowNumSales

AutoGenerate vEndDateSales - vStartDateSales + 1;