1 Reply Latest reply: Mar 9, 2016 6:20 AM by Kaushik Solanki RSS

    SSTG (Same store sales growth)

    Sachindra Sahu

      I have to create a column chart of SSTG [Same stores transaction growth]

      Context – Suppose we have the data for 3 years from 2013 (Jan) to 2015 (Dec) and we have 14 stores in 2013, 15 in 2014 and 18 in 2015.

      SSTG is calculated on a monthly basis. For example growth in the number of transactions in each month

      (Jan 2014 to Jan 2013, Feb 2014 to Feb 2013 and so on).

      When SSTG is calculated from 2014 to 2013 - Only those stores should be considered that were present in 2013 (14) not 15.

        • Re: SSTG (Same store sales growth)
          Kaushik Solanki

          Hi,

           

          To calculate this, you should have store opening date and store closing date. If you have this, the logic goes this way.

           

          Let varMin = Num(makedate(2013,01,01));

          Let varMax = Num(today());

           

          Temp:

          Load

          Date($(varMin) + Rowno() - 1 )as Date

          AutoGenerate 1

          while Date($(varMin) + Rowno() - 1) < Date($(varMax));

          ;

           

          Master:

          Load

          Distinct

          Date(Floor(MonthEND(Date)),'DD-MM-YYYY') as Cal_Date

          Resident Temp;

          drop Table Temp;

           

           

          Store_Table:

          LOAD Distinct

             

              Code as Store_NO,

             

              Date(Floor("Opening Date"),'DD-MM-YYYY') as Store_Opening_Date,

              Date(Floor(If("Closing Date" = makedate(1753,1,1),Makedate(9999,01,01),"Closing Date")),'DD-MM-YYYY') as Store_Closing_Date

             

             

          FROM Store;

          join

          Load

          Cal_Date

          Resident Master;

          drop table Master;

           

          SSSG:

          load

          *,

          Year(Cal_Date) as Financial_Year,

          if(Monthname(Store_Opening_Date)>=monthname(Cal_Date) or Monthname(Store_Closing_Date)<monthname(Cal_Date),0,1) as Curr_year_flag,

          if(monthname(Store_Opening_Date)>monthname(addyears(Cal_Date,-1)),0,1) as Prv_year_flag,

           

          if(Monthname(Store_Opening_Date)>=monthname(Cal_Date) or Monthname(Store_Closing_Date)<monthname(Cal_Date),0,1)

          +

          if(monthname(Store_Opening_Date)>monthname(addyears(Cal_Date,-1)),0,1)as SSSG_Flag

           

          Resident Store_Table

          ;

           

           

          drop table Store_Table;

           

           

          noConcatenate

           

           

          NEW_SSG:

          LOAD

              Store_Opening_Date,

              Store_Closing_Date,

              Store_NO,

              Cal_Date,

              Financial_Year,

              Curr_year_flag,

              Prv_year_flag,

              SSSG_Flag,

             if(WildMatch(SSSG_Flag,'2'),'L2L') as L2L

            

             resident

          SSSG;

          drop table SSSG;

           

           

          Regards,

          Kaushik Solanki