Do you feel that you are Tableau Expert? Then try this challenge 12

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

Experiment… Observe… Learn…