6 Replies Latest reply on Dec 14, 2016 8:57 AM by Nagaraj Ganiga

    Need help to solve YoY growth with some conditions!

    Nagaraj Ganiga



      I need to calculate YoY growth based on some conditions for the data I'm attaching.

      Like you can seen in the attachment Image1 I need to calculate the YoY.

      Note:  The data is in reverse oredr (means from 2016 to 2008)


      Same formula

      ((Current Year Sales – Previous Year Sales) / Previous Year Sales) * 100


      But I have some rules while calculating YoY Growth.

      Rule No 1:

      If any year does not have sales, then YoY growth for following years should be NULL.


      For example, in Central, 2016 does not have sales so 2016 onward till 2008 there is no year over year growth.


      Similarly, you can see South, 2014 onward and West 2015 onward YOY is empty.


      Rule No 2:

      While calculating YOY if there is no data for previous year then, you need to use the year before previous year.


      For example, in west region, 2016 I don’t have sales in 2015 so I took sales of 2014 and then calculated YoY.


      ((128-81) / 81) * 100 = 58%


      If the year before previous year then can display YoY as NULL for that year.


      For Example, in south 2014 year, there is no sales in 2013 and at the same time there is no sales for 2012 also. So, YoY for 2014 is NULL.


      In the Image2(Attachment) Assume I have data till 2014. I just filtered above data for this example.


      For Central 2014:

      ((157-180) / 180) *100 = -13%


      For Central 2013:

      ((180 – 196) / 196) *100= -8% (According to Rule 2)


      For Central 2012 onwards YoY is NULL (According to Rule 1)





      Thank you.