Retail Sales LFL Measure (…made Simple with LoD Calculations)

Version 4

As a retail analyst for the last 10 years, true LFL sales is a bit of a 'Holy Grail'. With LoD Calculation, the calculation of these has become very simple.

Just to ensure we are all on the same page, I will first define what I mean by LFL sales.

Definition: LFL Sales is the YoY (Year on Year) performance (generally Sales) when stripping out all new stores and closures.

Reason: Looking at total YoY sales, although a valuable measure, doesn't take into account estate growth/shrink. This can mask underlying problems with the business. For example if a Retailers total YoY is looking positive, but they have been opening lots of new stores, the LFL measure will strip out the new stores, so looking at the ‘organic’ growth only. Opening stores is an expensive way of growing a retail business, and can just be taking sales away from existing stores. So looking at LFL (as a YoY variance) at, say, a City level will indicate if a business is growing sale through store openings, or cannibalising existing store in that city.

There are several methods for looking at LFL. The simplest, is just to take all stores that have been open during the entire duration you are looking at. Personally I think this ‘throws’ away too many data points, so I prefer this to be looked at on a Week by Week basis.

The table below shows how LFL sales are calculated, and how it differs from total sales

Usually LFL measures come out of reporting systems, ‘Pre-Canned’ with the exact rule set unknown (often set up years ago)…Does it include E-Comm, All Store formats…etc.? This also restricts the freedom an analyst wants to explore a data set ‘their’ way.

Traditionally I've calculated my LFL flag within the ETL…but this isn't an option for many users. In T8 you could do it, but you always needed Week/Year/Site as your Level of Detail in any Viz (which creates lots of performance and visualisation problems downstream…

So LoD to the rescue…

With the LoD Calculation, the generating of a LFL Flag (i.e. was this store open in this week for both this year and last), is very simple (almost embarrassingly so!)

The LFL flag I want is to say if for a given week, and store was open both years. I use the LoD formula

{FIXED [Store Code], [Financial Week]: COUNTD([Financial Year])}

I can then create my new LFL Sales Field with the following formula

IIF ([LFL Indicator LoD] = 2, ZN([Sales Value], NULL)

And that’s it! (btw I've created 2 fields here for understanding, but could nest them in one).

If your data set has more than 2 years in, you can use {FIXED: MAX([Financial Year]} and an if statement to only bring back the last 2 years in the form (or a variation on this to look at any 2 years)

IIF([Financial Year] >= LoD Max Year – 1, [Financial Year], NULL)

And then do the LFL Flag calculation on that field.

I can also think of many other use cases, where you only want to compare equivalent things in a year on year calculation (to understand the ‘organic’ growth)

Say…

{FIXED [Product], [Financial Week]: COUNTD([Financial Year])}

{FIXED [Customer], [Financial Week]: COUNTD([Financial Year])}

I've attached a worked example, and even done a nice Viz (well I like it!) showing both LFL and Total Sales variance for my dummy data-set.

Happy Analyzing!

Edit SR 20/06/2016: I've also added a way to do this using sets, for those in Tableau 8

Edit SR 15/042018: Following a question below from Trinadh Singaladevi I have added 2 methods for handling multiple years LFL.

First thing, a bit about multiple years LFL; When assessing LFL over multiple years we need a set of LFL for each year variance. If we look at some dummy data for this, I can demonstrate what I mean.

Store B for Weeks 3 & 4 is not in the 2013 list for 2012 vs 2013 LFL (as it wasn't open in 2012), but Weeks 3 & 4 for Store B are included in the 2013 vs 2014 LFL calculation. This is where the complication arises (we need to assess each store for each Year vs Year combination.

Option 1: Using LoDs. The advantage of this is that it's simpler to understand, and the disadvantage is that we need a calculations for each year combination...meaning we need lots of calculations and as the data updates to the next year, the model will need maintaining as a new set of calculations will be needed.

We set up a check (very similar to the single year method for each year on year combination)

[2012/2013 LFL Flag]

{FIXED [Store], [Week]: COUNTD(IIF([Year]=2012 OR [Year]=2013, [Year],NULL))}=2

[2013/2014 LFL Flag]

{FIXED [Store], [Week]: COUNTD(IIF([Year]=2013 OR [Year]=2014, [Year],NULL))}=2

and then we can use these flags in each of the YoY calculations

[LoD - 2012/2013 - LFL Var]

(SUM(IIF([Year] = 2013 AND [2012/2013 LFL Flag], [Sales],NULL))

/

SUM(IIF([Year] = 2012 AND [2012/2013 LFL Flag], [Sales],NULL)))

-1

[LoD - 2013/2014 - LFL Var]

(SUM(IIF([Year] = 2014 AND [2013/2014 LFL Flag], [Sales],NULL))

/

SUM(IIF([Year] = 2013 AND [2013/2014 LFL Flag], [Sales],NULL)))

-1

And when we plot our chart, we need to use the measure names/measure values

Option 2: Using Table Calculations. Advantages is that it's self-updating, so your model won't need annual maintenance and if you've got 10 years you won't need 20 calculations. The disadvantage is that it's bit more complicated!

first thing to say is that we are going to need Store, Week and Year in the VizLoD, so the Table Calcs can run the logic over these levels.

First we create this calculation to pick up the first year (this won't have a YoY var as it has no previous year)

[First Year of Data]

[Year] = {MIN([Year])}

We, also, create this calculation (to bring back the sales for the previous year)

[Sales Previous Year]

LOOKUP(SUM([Sales]),-1)

The we use this calculation to bring back only this years sales (if the Store/Week combination has sales for both years)

[TC - LFL Sales Var - Rolling Years TY]

//First Year (no Comp)

IF ATTR([First Year of Data]) THEN NULL

ELSEIF NOT(ISNULL(SUM([Sales]))) AND NOT(ISNULL([Sales Previous Year])) THEN

SUM([Sales])

END

and the same to bring back the previous year (on satisfying the same conditions)

[TC - LFL Sales Var - Rolling Years LY]

//First Year (no Comp)

IF ATTR([First Year of Data]) THEN NULL

ELSEIF NOT(ISNULL(SUM([Sales]))) AND NOT(ISNULL([Sales Previous Year])) THEN

[Sales Previous Year]

END

We can then bring these into our Viz, and set up as follows (for both Calcs). NB Although the default Table Across works when we are in a Table, we are going to move the pills about so important to be explicit about the Table Calculation compute using)

and we get this

Once we have these we can use these to create the LFL var

[TC - LFL Sales - Rolling Years Var]

WINDOW_SUM([TC - LFL Sales Var - Rolling Years TY])

/

WINDOW_SUM([TC - LFL Sales Var - Rolling Years LY])

-1

We set this up like this

and we get

So the correct answer, but too many marks!

So we add one more calculation for our final version

[TC - LFL Sales - Rolling Years Var FINAL (single mark)]

IF FIRST() = 0 THEN [TC - LFL Sales - Rolling Years Var] END

in addition to the above, we set this extra nested Table Calculation like this

So now out Table looks like

Almost there! We can now move the pills about to get the final viz

I've attached both methods (T 10.4), with working and Tables...hope you find this useful.