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

# Need help to solve YoY growth with some conditions!

Hi

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.

• ###### 2. Re: Need help to solve YoY growth with some conditions!

Hi Shawn Wallwork any Idea?

• ###### 3. Re: Need help to solve YoY growth with some conditions!

Hi Nagaraj,

Here's a view through 2014:

What made this confusing for me (and I expect why you haven't gotten any responses yet) is that in your problem description there are terms like "previous", "following", and "onward" used and it's apparent that the calculations need to go in two different directions (year ascending and year descending). I was able to parse it out only by using the combination of the examples and the screenshots and a certain amount of head-scratching. If you have questions like this in the future please be careful to not use directional terms like previous/following/onward/first/last/next without being very explicit about the ordering of the data.

Now, before you go through this solution please watch this video Re: TDT with Jonathan Drummey - July 10, 2014 , you'll also want to check out the linked posts here: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers . The reason why is that this solution makes extensive use of nested table calculations and data densification.

The view has Region and Year as dimensions, with Year sorted in Descending order. The data is sparse, there is not a record for every Region/Year combination. In order for the lookups to work we need to pad (densify) the data, we do that with a table calculation (actually there are three table calculations) that has a compute using on Year. In this crosstab layout that triggers crosstab domain completion and the view is densified.

Here's a quick walk through the calcs:

Max Year - gets the maximum year in the (filtered) data, has a compute using on Year & Region.

Padded Year - generates the particular (densified) year via a calculation using Max Year, has a nested compute using on Year.

Rule 1 - uses the Padded Year and returns a boolean value on whether or not the particular mark passes Rule 1 or not, has a nested compute using on Year

Rule 2 Prior Year - returns the prior year sales per Rule 2, it has a compute using on Year with a Custom Sort of Year/Min/Ascending.

% Diff - the calculation that returns the % diff with Rule 1 and Rule 2. So this calculation has 4 table calculations nested inside it.

As you build this view on your data you're going to need to pay very close attention to the compute using settings of each table calculation and the nested settings of each calculations' parents, which is why I recommended checking out the materials above.

v10.0 workbook is attached.

Jonathan

3 of 3 people found this helpful
• ###### 4. Re: Need help to solve YoY growth with some conditions!

Thanks for correcting me, I'm new to tableau.

The screenshot of the sheet looks fine but I'm not able to open the attached workbook since the workbook doesn't exist in Tableau Public. It will be very helpful if you can share the the screenshot of calculation field you have used.

Thanks again.

Nagaraj

• ###### 5. Re: Need help to solve YoY growth with some conditions!

Here's a link to the workbook on Tableau Public: Workbook: table calcs with different sort

1 of 1 people found this helpful
• ###### 6. Re: Need help to solve YoY growth with some conditions!

Thank you Jonathan Drummey learnt so much.