Qlik Community

QlikView Documents

Documents for QlikView related information.

Like For Like (Comparative) Sales Analysis (Flag Method)

nick_scott
New Contributor III

Like For Like (Comparative) Sales Analysis (Flag Method)

Background

“Like for Like” or “Comparative” sales analysis is a common requirement in the Retail sector.  The objective is to assess growth from the underlying “business” by comparing the business on a “like-for-like” basis with the business in the previous period.

The comparable basis can be manifold and will depend upon the business analysis requirement:

Comparable basis

Example

Purpose

Like for Like Store Sales

Multi-site retail

This compares the stores open in the current period with the same stores in the previous period

Like for Like Brand Sales

Property management

Shopping centre

this compares the sales from the same brands in the current period with the same brands in the previous period

Methodology – Transaction Flags

The conceptual basis of the “like-for-like” analysis is to identify or “flag” the transactions that are relevant for the comparison (i.e. are comparable sales).  This simple approach has significant benefits

  • Easily validated in the data table
  • Simple to express in expressions
  • Low RAM usage in the application

Flagging Transactions

Taking the example of the Like for like store sales, where the requirement is that the store was “open(1)” in the both "periods"(2), we can take the following logical approach:

Calculate two Like For Like Flags

  1. Flag TY - Identify those transactions in the previous year which are comparable to the current year (TY); AND
  2. Flag LY - identify those transactions in the current year which are comparable to the previous year (LY).

Image 1: Illustrative example of Like-For-Like Flag Method

Like-For-Like_table.bmp

The methodology can be applied to create any like-for-like analysis and with the use of set analysis can be dynamic in the application.

Result

The analytical result of this is

  • Sales growth of 18% (659 / 557 - 1); but
  • Like-for-Like sales growth of 32% (387 / 512 -1)

Note

(1) "Open" - can be defined in many ways.  In this example it is that the store had sales in the comparable period.

(2) "Period" - this will require careful definition.  The period could be the same "week" last year or on the same calendar date.  Where the company uses the 52 week retail calendar (4-5-4) there will be a week 53 every 7 years!


Explore & Enjoy,

Nick Scott.

Attachments
Comments
Not applicable

Nick is it possible to load a qvw model and data for this post- great post however

nick_scott
New Contributor III

Hi

The file is now attached (it appears not to have been with the original post).  It's a common issue, so I hope that you and others find this useful.

Explore & Enjoy,

Nick Scott.

mariia_gridneva
New Contributor II

Hello Nicholas,

Would you mind to solve your function on date level? It works perfectly on year, month and week, but loses on day level.

Here comes your variable script: vSales_TW_LY

sum({$<Year=, Month=, [Week Name] = , Date = {">=$(=date(weekstart($(vDate),0)-364,'DD/MM/YYYY')) <=$(=date(weekend($(vDate),0) -364,'DD/MM/YYYY'))"}>}Amount)

What I need is possibility to compare LFL-dates by selecting only tex. 3 days in a row.

nick_scott
New Contributor III

Hi

If you amend the set analysis you can compare at a day level.  You will only need to be careful if you are in a 53-Week Year that you are comparing appropriately.

Develop & Discover

Nick Scott.

Not applicable

Hi Nick, i'm struggling with this part since the last 1 week

My Requirement is actually this if a user selects the current month the higest 2 values in current month

and i want to see how these higest values are in previous 3 months user can select any month,
if he selects any month the previous 3 months data should appear. please help me out

Table:

LOAD * INLINE [

  Country, MonthYear, Value

  Argentina,Jun2016,550

  Japan,Jun2016,200

  America,Jun2016,100

  America,May2016,300

  Argentina,May2016,250

  Japan,May2016,150

  America,Apr2016,200

  Argentina,Apr2016,170

  Japan,Apr2016,210

];

My output is below

Version history
Revision #:
1 of 1
Last update:
‎10-21-2013 09:15 AM
Updated by: