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.
table calcs with different sort.twbx 157.7 KB
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.