Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Rolling 52 Week Comparison (53-Week Year Problem)

nick_scott
New Contributor III

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 ap...

Attachments
Comments
Not applicable

Thanks for this, really useful document.  Working closely with our commercial teams, the need to have a comparison against the previous year (i.e. 52 weeks earlier) is vital, not optional so this has been a great help.

Thanks,

Jason.

ajsjoshua
Contributor III

Hi ,

Seems to be vital one,pls share the script i am using personal edition.

Regards,

Joshua.

stantrolav
Contributor II

I used if:

num(Week(DateNum), '00') as CalWeek52,

num(if(Week(DateNum) = 1 and Month(DateNum) = 12, 53, if(Week(DateNum) = 53 and Month(DateNum) = 1, 1,Week(DateNum))), '00') as CalWeek53,

Version history
Revision #:
1 of 1
Last update:
‎10-02-2013 06:33 AM
Updated by: