4 Replies Latest reply: Jan 31, 2016 7:40 AM by Hirish V RSS

    YTD Month on Month growth computation

    Rakesh Paul

      Hello All,

       

      I am trying to calculate the sales cumulative growth, lets say I want to see the YTD month on month sales growth. Lets me be more elaborate -

      Lets say I am on April 2015, so when I will calculate the growth it word be nothing but the ((April Sales 2015/April Sales 2014)-1)% and when I am looking on August 2015 Sales growth then formula would be (((April sales 2015+May sales 2015+June sales 2015+July sales 2015+August sales 2015)/(April sales 2014+May sales 2014+June sales 2014+July sales 2014+August sales 2014))-1)%

       

      I am attaching the data and desired the output also-

       

          

      Month20142015Cumulative 2014Cumulative 2015Growth
      Apr 11,355,827.30 11,688,010.89      11,355,827.30      11,688,010.89 2.93%
      May 12,332,445.78 14,450,906.30      23,688,273.08      26,138,917.19 10.35%
      Jun 13,994,001.11 14,773,599.56      37,682,274.19      40,912,516.75 8.57%
      Jul 13,714,112.69 13,975,539.26      51,396,386.88      54,888,056.01 6.79%
      Aug 14,492,648.48    7,703,767.47      65,889,035.36      62,591,823.48 -5.00%
      Sep   7,632,931.03 14,180,434.15      73,521,966.39      76,772,257.63 4.42%
      Oct 12,430,265.12 12,724,260.52      85,952,231.51      89,496,518.15 4.12%
      Nov 13,242,570.18    6,719,885.34      99,194,801.69      96,216,403.49 -3.00%
      Dec 15,322,086.06 14,666,806.41    114,516,887.75    110,883,209.90 -3.17%
      Jan   9,854,753.18 16,194,947.55    124,371,640.93    127,078,157.45 2.18%
      Feb 14,201,979.18 14,827,498.76    138,573,620.11    141,905,656.21 2.40%
      Mar 13,768,417.32 12,862,753.16    152,342,037.43    154,768,409.37

      1.59%

       

      I used the foll wing formula to calculate the Cumulative 2014 & Cumulative 2015 Sales and it's coming correctly-

      Cumulative_2015: Sum({<Year={"$(V_Curr_Year)"}>}Sales), where V_Curr_Year = Num(right(GetCurrentSelections(Year),4))

      Cumulative_2014: Sum({<Year={"$(V_last_Year)"}>}Sales), where V_Curr_Year = Num(right(GetCurrentSelections(Year),4))-1


      And after that I tried to use these following formula to calculate the growth-

      ((Cumulative_2015/Cumulative_2014)-1)%


      But though Cumulative value was correct I not getting the Growth properly. Please let me know where I am doing wrong and how I can fix this issue.


      Regards,

      Rakesh Paul



        • Re: YTD Month on Month growth computation
          Hirish V

          Hi,

           

          Please post your sample data  in excel sheet,

           

          Date and Sales columns

           

          Hirish

          • Re: YTD Month on Month growth computation
            Hirish V

            Hi,

            Check this ,

             

            using your data,

            Data:
            LOAD * INLINE [
                Month, Sales, Year
                Apr, "11,355,827.30", 2014
                May, "12,332,445.78", 2014
                Jun, "13,994,001.11", 2014
                Jul, "13,714,112.69", 2014
                Aug, "14,492,648.48", 2014
                Sep, "  7,632,931.03", 2014
                Oct, "12,430,265.12", 2014
                Nov, "13,242,570.18", 2014
                Dec, "15,322,086.06", 2014
                Jan, "  9,854,753.18", 2014
                Feb, "14,201,979.18", 2014
                Mar, "13,768,417.32", 2014
                Apr, "11,688,010.89", 2015
                May, "14,450,906.30", 2015
                Jun, "14,773,599.56", 2015
                Jul, "13,975,539.26", 2015
                Aug, "  7,703,767.47", 2015
                Sep, "14,180,434.15", 2015
                Oct, "12,724,260.52", 2015
                Nov, "  6,719,885.34", 2015
                Dec, "14,666,806.41", 2015
                Jan, "16,194,947.55", 2015
                Feb, "14,827,498.76", 2015
                Mar, "12,862,753.16", 2015
            ];
            
            
            
            
            
            
            
            
            
            
            
            
            

             

            Front end:

            Year - Year Accumulation-203638.PNG

             

            Here i have created a straight table,

            Expressions:

            For 2014      =Sum({<Year={"$(=Max(Year)-1)"}>}Sales)
            For 2015      =Sum({<Year={"$(=Max(Year))"}>}Sales)  
            For 2014   Accumulation  =Rangesum(Sum({<Year={"$(=Max(Year)-1)"}>}Sales),Above(Cummulative_2014))
            For 2015   Accumulation  =Rangesum(Sum({<Year={"$(=Max(Year))"}>}Sales),Above(Cummulative_2015))
            
            

            Growth Rate  =((Column(4)/Column(3))-1)  For this expression in number format fixed to 2 decimal Show as percent.

             

            HTH,

            PFA,

            Hirish