Rolling 52 Week Comparison (53-Week Year Problem)

    The 53-Week Year Problem

     

    The 4-4-5 / 4-5-4 Calendar ("Retail/Merchandising Calendar")

    Using the "4-5-4 calendar" divides a year into 4 quarters. Each quarter has 13 weeks (4+5+4=13) which are grouped into two 4-week "months" and one 5-week "month". The business will report a 52 week year. However, approximately every 5th/6th year the business will have a 53rd week, making comparison between years/periods difficult. This anomaly has most recently occurred in FY'00, FY'06 and FY'12, and will occur in FY'17.


    Retail Analysis - Rolling 52 Weeks

    A Rolling 52 week comparison is a common analytical requirement. Frequently, however, due to the 53-Week Year the periods may not always be directly comparable.

     

    The Calendar

    The key to the analysis is the calendar.  Here it is best to create the calendar in an Excel table, detailing the YearWeek, WeekStart Date and the Week period number. The company will have a rule to define when the year end occurs, commonly:


    a) Last Saturday of the Month at Fiscal Year End
    Under this method the company's fiscal year is defined as the final Saturday (or other day selected) in the fiscal year end month. For example, if the fiscal year end month is August, the company's year end could fall on any date from August 25 to August 31.

     

    b) Saturday nearest the end of month
    Under this method the company's fiscal year is defined as the Saturday (or other day selected) that falls closest to the last day of the fiscal year end month. For example, if the fiscal year end month is August, the company's year end could fall on any date from August 28 to September 3.

     

    c) Industry Standard Merchandising Calendar

    Some companies will adopt an industry standard calendar like the National Retail Federation.  For more information see: 4-5-4 Calendar | National Retail Federation.

     

    QlikView example

    This example application demonstrates two approaches to transforming the weeks to provide a comparable 52 week analysis.


    1) Flags - determining the Last 52 Week periods in the script and creating a Comparable Week period; and
    2) Set Analysis - using a calculated dimension to dynamically manipulate the Week to create a Comparable Week.

     

    What are the rules determining how sales in a 53-week year are compared to the previous / subsequent periods?

    For comparability purposes, the 4-5-4 Calendar restates a 53-week year in the subsequent year (ex. 2017 is restated for comparability to 2018). This is accomplished by pushing each week of the 53-week year back one week, thereby ignoring the first week of the fiscal year (in this example, 2017).


    In the case of a Rolling 52 Week Analysis this transformation requires to compare both previous and subsequent periods.  A week is omitted in each period.


         Previous periods - Week 201701 becomes 201653, to compare with 201753 (i.e. is 52 weeks prior)

         Subsequent periods - Week 201753 becomes 201752, to compare with 201852 (i.e. 52 weeks prior)


    The benefit in transforming the weeks is to align holidays, which naturally account for a significant percentage of retailers’ sales. This is generally essential for a retailer and necessary when considering "like of like" analysis.


    Please post your comments,

     

    Explore & Enjoy,

    Nick Scott.

    github.png

    Discover more on GitHub: GitHub - nicholasscott72/Qlik-DimensionalModelling: A simple development methodology for QlikView applications, leveragi…