# QlikView Documents

Documents for QlikView related information.

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

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

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.

Labels (3)

• ### QlikView Applications

Not applicable

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

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.

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.

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,

Table:

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: