Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hello,
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 butitsnot showing 2016 option due to the format of my QuarterYear. How to fix this too?
I didn't your question right. For 2016 data, use 2016 Jan 1 as your start date
LET vStartDate = Num(Floor(YearStart(Today(), -2)));
And then to remove additional dates left join the calendar to your data model.