Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

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.

stephane_kosior
New Contributor III

How to change the Fiscal Year ?

Hi,

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,

Stephane

SET vFiscalYearStartMonthSales = 9;

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

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

FiscalCalendarSales:

LOAD

*,

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

LOAD

*,

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

LOAD

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

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

AutoGenerate vEndDateSales - vStartDateSales + 1;