7 Replies Latest reply: Sep 18, 2017 3:04 PM by Vadim Grab RSS

    Like for Like Sales Calculation

      Attached there is a qlikview file gathering data from Excel file for Sales per Store. As a retail company It is significant to Compare Sales per store and Company Totals on Daily basis with the exact day last year. In my example there are three stores: Store A, B and C. Store A opened doors on 01/10/2011 and steel open till today. Store B opened doors on 04/10/2011 and still open and Store C is opened on 01/10/2011 and closed on 06/10/2011. The target is to select one date in 2012 and the table return the sales for that day per store, the sales for that day last year and Company totals for Day to Day, month to month and year to year comparison only for operational dates.

       

      Thanks in advance.

        • Re: Like for Like Sales Calculation
          Gysbert Wassenaar

          See attched qvw. Is that what you need?

            • Re: Like for Like Sales Calculation

              I have to say that is a very interesting aproach but the target is to calculate sales, let's say for the B store this year sales have to be calculated from the correspending date of the openening day (04/10/2012). And for the C Store respectively last year month sales have to be calculated till the last day working of this year. (02/10/2011).

               

              Thank you anyway.

               

              Note: I have update the data file. and used with your qlikview file.

                • Re: Like for Like Sales Calculation
                  Henrik Matz

                  I would create a like for-like-dimension table for each combination of store and date with a flag weather this combination is Like-for-like acceptable.

                   

                  I would also create a calendar dimension table with a corresponding like for like date for each date in the calendar.

                   

                  Then you should then be able with some set analysis to make your calculation on all measures in your fact table.

                   

                  /Henrik

              • Re: Like for Like Sales Calculation
                Nicholas Scott

                Like for Like Sales

                It is common to require to do a "like-for-like" comparison in the retail sector.  An approach is to use "flags" in the script and to use these flags to calcuate the comparable sales amounts for the current & prior periods.  The script calcualtes flags, at a  record level for:

                 

                a) "comparable" sales in Current Period

                b) "comparable" sales in Prior Period

                 

                The basis of comparison usually requires to be considered carefully.  In a simple example (as in your data set) the key is that the Store is open on the same Date in the previous year; this provides (a) the comparable sales in the current period.  However, it is also necessary to calculate (b) the "comparable" sales in the prior period to ensure that the comparison reflects any closures in the current period.

                This is a generic approach, and I hope it helps in your case.

                To achieve your analysis requirements you will require to use Set Analysis to enable the selection of a Date and the calcualtion of the Month & Year values. I have included some basic set analysis.