Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
stephane_kosior
Creator
Creator

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;

0 Replies