Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Can anyone let me know how to return the current Fiscal Month Number based on todays date.
26 September 17 = 6
14 May 17 = 2
Etc
Many thanks
Phil
PFB Fiscal and Standard Calendar generation
Hi Phil,
Please Find below Script:
It will work For you:
SET vFiscalYearStartMonth = 4;
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num(AddYears(Today(),1));
FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
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;
Thanks,
Arvind Patil