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
- 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;
