Custom Fiscal Calendar

    All,

     

    I have done some searching around the Qlik Community and found plenty of helpful documentation on creating a Fiscal Calendar that starts on the first day of a month.  Adding to that, I have developed a way to start the Fiscal Calendar on a particular day of the month.  If this has already been posted (I couldn't find anything) I apologize.

     

    Assumptions:

    1.  Fiscal Year starts on the Thursday before the first Saturday in October.

    2.  Fiscal Year is 12 Periods (Months), 52 Weeks (Thurs-Weds each week), 364 Days.

    3.  Fiscal Months sequence is 4-4-5 (4 weeks, 4 weeks, 5 weeks, repeat sequence).

    4.  Data is to be reported at the weekly grain

     

    Script:

    //Custom Fiscal Calendar starting on the Thursday prior to the first Saturday in October:

     

    //-------------------------------------------------------------------------------------------------------------------------------

     

    set vFM = 10; //start the fiscal calendar on first day of October

    set vFSD = 5; //set the fiscal start day of October to Saturday

    set vFD = 3; //set the first day of the fiscal week to Thursday

     

    //-------------------------------------------------------------------------------------------------------------------------------

     

    MapPeriod:

    mapping load

           FiscalWeekNum,

            FiscalPeriod

    FROM

    [Source.xlsx]

    (ooxml, embedded labels, table is FiscalMap);

     

    MapWeekOfPeriod:

    mapping load

           FiscalWeekNum,

            FiscalWeekOfPeriod

    FROM

    [Source.xlsx]

    (ooxml, embedded labels, table is FiscalMap);

     

    //-------------------------------------------------------------------------------------------------------------------------------

     

    //load data in at weekly grain.

    Fact:

    NoConcatenate

    load

           'P' & num(FiscalPeriodNum,'00') & '-' & num(FiscalWeekOfPeriod,'00')        as FiscalPeriodWeek, //concatenated field for front-end Users to select on Weekly tab

           'P' & num(FiscalPeriodNum,'00') & '-' & num(FiscalWeekOfPeriod,'00') & '-' & FiscalYearNum     as %PeriodWeekYear_KEY, //concatenated key for scripting

           *

    ;

    load

           applymap('MapPeriod',FiscalWeekNum,null())               as FiscalPeriodNum, // map the week # (1-52) to a period (1-12)

           applymap('MapWeekOfPeriod',FiscalWeekNum,null())   as FiscalWeekOfPeriod, //map the week # (1-52) to a week of period (1-5) based on 4-4-5 calendar

           *

    ;

    load

            ceil((Date - StartOfWeekOne +1) / 7)      as FiscalWeekNum, //Dates grouped into weeks (1-52) based on the start of the work Fiscal Year

           *

    ;

    load

           weekstart(FiscalYearStart,0,$(vFD))       as StartOfWeekOne, //Start the work Fiscal Year on the first Thursday prior to the first Saturday of the Fiscal Year

           weekend(FiscalYearStart,0,$(FSD)-1)     as StartofFiscalYear, //check to make sure FiscalYearStart is working correctly     

            *

    ;

    load

            (weekend(yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM)),0,$(FSD)-1) - yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM)))

                        + yearstart(num(date(floor(Date),'MM/DD/YYYY')),0,$(vFM))           as FiscalYearStart, //Start the regular Fiscal Year on the first Saturday of October

           Year(num(date(floor(Date),'MM/DD/YYYY')))                                 as Year, //Calendar Year

           month(num(date(floor(Date),'MM/DD/YYYY')))                                as Month, //Calendar Month

           num(date(floor(Date),'MM/DD/YYYY'))                                       as Date //raw field value comes in as "MM/DD/YYYY"...convert this to a number

    FROM

    [Source.qvd]

    (qvd)

    where num(date(floor(Date),'MM/DD/YYYY')) >= 41914

    ; //load data starting on first day of work fiscal year 2015

     

    Please post any comments/questions.

     

    Thanks,

    Tyler