Using below data try to solve this challenge.
To download this data, click here
Below Image I am showing Year over year growth based on some rules.
Please follow rules and try to solve this challenge.
Generally, Year over Year(YoY) growth calculated using below formula.
((Current Year Sales – Previous Year Sales) / Previous Year Sales) * 100
But I have some rules while calculating YoY Growth.
Note: we are computing data from left to right (means from 2016
to 2008) in above image.
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
onwards till 2008 there is no year over year growth.
Similarly, you can see South, 2014 onwards and West 2015 onwards YOY is
empty.
Rule No 2:
While calculating YOY if there is not data for previous year then, you
need to use before previous year data.
For example, in west region, 2016 we don’t have sales in
2015 so I took sales of 2014 and then calculated YoY.
((128-81) / 81) * 100 = 58%
If before to previous data is not there then you 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.
See one more example below, 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)
Note: This is not very complex challenge. I just used only one
calculation filed to display YoY. I am giving clue also. I used only 4
functions along with if statement. Out of four functions two functions that I used
ISNULL and SUM.
For more challenges: Click Here
For more challenges: Click Here
Experiment… Observe… Learn…