4 Replies Latest reply on Jun 22, 2016 10:01 AM by Simon Runc

    LFL Sales Solving without using LOD

    Shivang Desai

      Hi All,

       

      I am trying to solve this LFL this for a long time but not able to implement properly.

       

      I need to calculate 2 LFL's:

       

      1) LFL Same Week Last Year = (Curent Full Week Sales - Same Full Week Last Year Sales / Same Full Week Last Year Sales)

       

      2) LFL Previous Week = (Current Full Week Sales - Previous Full Week Sales / Previous Full Week Sales)

       

      But there is an important condition for this:

       

      If for current full week, a sale is made by a particular restaurant, only that restaurants same full week last year sales should be taken into account.

       

      Example:

                                         Current Full Week                    Same Full Week Last Year

      Store

      A                                      $100                                                $34

      B                                      $34                                                  $32

      C                                                                                              $21

      D                                      $234                                                $400

      E                                                                         

       

      Here C and E same full week last year shouldn't be considered as there are sales for current full week.

       

      So the Current full week sales should be (100+34+234) =   $368

                 Same full week last year sales should be (34+32+400) = $466

       

      LFL (same full week last year) = (368-466)/ 466

       

      I have attached sample data (In that data, store names AUBU and MOUNTY same week last year sales shouldn't be included).If you have any queries, plese feel free to ask.

       

      Moreover, the important issue for me is to get a graph for year 2015 and 2016.

       

      Please guys help me with this!

       

      Jonathan Drummey Tharashasank Davuluru Mahfooj Khan

       

      Kind Regards,

      Shivang Desai

        • 1. Re: LFL Sales Solving without using LOD
          Simon Runc

          Hi Shivang,

           

          Just a couple quick questions...do you have the field 'New Week Status' in your data, or do we need to calculate if a store falls into one of these classifications (Current Full Week, Previous Full Week, or Same Full Week Last Year) within Tableau?

           

          and on the graph....do you want to show it by week? or just a single value? if it's just a single value, how do we do this for the 2nd option...as this (and it could be my lack of understanding) would be a rolling value, where it would change week to week....Week 17 vs Week 16, Week 16 vs Week 15...etc.

          • 2. Re: LFL Sales Solving without using LOD
            Shivang Desai

            Hi Simon,

             

            Thanks!

             

            Here are the answer to your questions.

             

            do you have the field 'New Week Status' in your data, or do we need to calculate if a store falls into one of these classifications (Current Full Week, Previous Full Week, or Same Full Week Last Year) within Tableau?

            Ans:  Yes, my data has fields called New Week Status and it has values (Current Full Week, Same Full Week Last Year , Previous Full Week, Previous Full Week Last Year).

             

            on the graph....do you want to show it by week? or just a single value? if it's just a single value, how do we do this for the 2nd option...as this (and it could be my lack of understanding) would be a rolling value, where it would change week to week....Week 17 vs Week 16, Week 16 vs Week 15...etc

            Ans: Yes, on the graph I want to show it by week to week. I am hereby attaching a screenshot of visualisation needed so that it gives a better understanding

             

             

            SCREEN1

            Screenshot (142).png

             

            SCREEN2  (purple line - previous financial year,  brown line - current financial year)

            Screenshot (143).png

             

            As you can see in the first screenshot, the LFL % Growth is 4.6%  This should be same as second screenshot Week 17 (current full week).

             

            If you have any doubts or dint understand anything, pleease let me know. Have edited the LFL LAT file which also shows that data is not just for Current Full Week, Previous Full Week, ...there is data where New Week Status is blank and need to plot it via a graph

             

            Kind Regards,

            Shivang Desai

            • 3. Re: LFL Sales Solving without using LOD
              Shivang Desai

              Hi Simon,

               

              I would like to add a few things more, may be this can help:

               

              So I too have a field called Last Year Net and Last Week Net, this field directly aligns with the Net column for each row (have attached the excel).

               

              Thanks

              • 4. Re: LFL Sales Solving without using LOD
                Simon Runc

                ...so a bit confused here (could well be me!!)

                 

                So you've said that the data you have will contain a [New Week Status], so we can use these to work out what is a LFL Week/Store (include) and what is not (exclude), so we don't need to create this within Tableau....but then you've said " ...there is data where New Week Status is blank and need to plot it via a graph" so looks like we do need to calculate if a store/week combination needs to be included in the LFL calculation? If so, what are the [New Week Status] field for? (can I just ignore it and calculate the LFL Store/Week combinations in Tableau?)...I might have to use this field for your Week on Week LFL version...as this is a bit trickier in Tableau.

                 

                Do I need to use the Last Year Net?...looks like I have year (for both years) in the data, so can I not just use 'Net Sales'? (for both TY and LY). I can do either, it just tends to be easier (in Tableau) to have a single year/week column, and single measure column (if I were doing this is an Excel Pivot Table, I'd definitely want both fields (TY Sales, LY Sales), but as Tableau thinks like a database it's easier to have just dimensions and measures)

                 

                The 4.6% we're aiming for (so we know when we get the right calculation)....is this the LFL var for week 17, or for the YtD total (so Weeks 1-17 LFL variance)