I came across and used code for the generation of a Fiscal Calendar from a previous post. There are a few lines of code though that i need a little clarity on as i want to be able to understand and explain it properly. Can anyone explain what they actually mean please. I left only the lines of code that i need explaining on.
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
*,
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,
1. Using Dual function we can assign two values to a field .. here FiscalQuarter will get text value of quarter for representation and numerical value underneath.
2. Mod(Month...............) : this is to set the starting month of your fiscal calendar.. $(vFiscalYearStartMonth) has to set before execution of script e.g: if April is the starting month of your fiscal calendar then set variable value to 4 then FiscalMonth get the value 1 for April month...
Same this is happening with YearName................
3. the last set of code is to generate a sequence of date in the range of vStartDate and vEndDate.
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
*,
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month -- Calculating the fiscal month, assume that Fiscal Year starts on April, then current month is December, then
(12 - 4)/12 + 1 = 9, 9th month of the fiscal year.
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year -- Gives the Fiscal year name based on the fiscal year starting month -- Refer qlikview help file for explanation
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date, -- Generate date field by adding rownum to mindate
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1; -- Looping for each date between min and max date
