Fiscal Year - Master Calender

    Hi All,

    Please find the Fiscal Year - Master Calender script and application.

     

     

    Set vFM = 4 ;
    LET vDateMin=num(makedate(2016,01,01));
    LET vDateMax=floor(monthend(today()));
    LET vDateToday = num(today());

    CalenderTemp:

    LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS Date,
    month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Month,
    year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Year,
    monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Monthname,
    Week(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Week
    AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

    Calender:
    Load *,Dual(fYear-1 &'/'& fYear, fYear) as FYear// Dual fiscal year
       Dual(Month, fMonthas FMonth   // Dual fiscal month
      ;
    load *,
    Year + If(Month>=$(vFM), 1, 0) as fYear// Numeric fiscal year
       Mod(Month-$(vFM), 12)+1  as fMonth   // Numeric fiscal month
    Resident CalenderTemp;
    drop table CalenderTemp;