Hi
I am using the below mentioned script to create a master calendar. Below mentioned are my problem statements
1. The current QuarterYear should be FY19 Q2 but its showing FY19 Q3 also which logically wrong. How to fix this issue?
2. I want to re frame my QuarterYear format from FYQ2-2018-2019 to FYQ2-2019. Because my model has a data for 2016 but its not showing 2016 option due to the format of my QuarterYear. How to fix this too?
SET vFiscalYearStartMonth = 4;
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num(YearEnd(Today()));
FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
'FY' & Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) & '-' &
YearName(Date, 0, $(vFiscalYearStartMonth)) as QuarterYear, // Fiscal YearQuarter
Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year(Date) AS Year, // Standard Calendar Year
Month(Date) AS Month, // Standard Calendar Month
Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
