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

Version 3

    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

    LFL Example Table.JPG

    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)


    {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