8 Replies Latest reply on May 9, 2014 10:49 AM by coco.rao.0

How to calculate year on year difference without using table calculation

I figured this would be easy enough, but after a day of trial and error, lots of error, nothing seems to work.

I am trying to calculate the absolute and percentage year on year difference for revenue and costs. The reason I don't want to do this in a table calculation, is that the table calc will automatically filter out the first date entry point of the selected period, even though for that date there is a previous year date available, it is just not being shown on the viz (filtered for a certain period)

For example I have a dataset with monthly entries over the period 2005-2013, I would like to see the YoY difference for only the months in 2013. So I filter for year 2013, now if I table calculate the YoY for 2013, then the table calculation will not return me anything as 2012 is not included in the table.

So now I am wondering, is there a way to calculate the YoY difference (absolute/percentage) without using table calculations?

Or am I doing it wrong and is there a way to do what I want (Only show specific time period, without showing a previous period just for the calculation) with table calculations?

Michel

• 1. Re: How to calculate year on year difference without using table calculation

http://mkt.tableausoftware.com/files/tip0911.pdf

See if this guide is helpful in your scenario.  If not, there are other options for hiding the previous year.

2 of 2 people found this helpful
• 2. Re: How to calculate year on year difference without using table calculation

Sorry for my late response, but this has helped us getting in the right direction!

Thank you Matthew!

• 3. Re: How to calculate year on year difference without using table calculation

Are you still needing help?  If not, make sure to mark your question answered so others know you've found a solution.  If the guide wasn't helpful enough, post a sample packaged workbook that demonstrates your data's structure and someone will be happy to help you with whatever your needs are. Cheers

• 4. Re: How to calculate year on year difference without using table calculation

I think the solution you provided, Matthew, will be the closest we will be able to get to solving the issue.

With your solution we are able to dynamically hide the empty cells which are generated by performing a year on year table calculation. There will still be two columns at the least, but at least only one will be visible.

What we would love to have, is the table calculation contained within a calculated field. In the way that if I Filter for a specific month, and thus only see the column related to that month, I will still be able to see a YoY metric. Thus not a lookup for the table, but a lookup in the dataset itself. Or perhaps a smart filter, one which filters all the data outside the data specified in the filter, but allows dependencies. For example, I filter for Jan2014 and want to see the YoY metric, then the filter will show me only Jan2014, but because the metric YoY needs Jan2013 as well it will allow this metric to still access the data from Jan2013 without showing Jan2013 as a separate column.

It is of course possible to solve this by putting in the metric in the database itself, but this is far from ideal.

• 5. Re: How to calculate year on year difference without using table calculation

I believe this can be done using Table Calculations.  Table Calcs are applied after all the data is pulled into Tableau, so we can still refer to a previous year while not showing it in the view.

I was trying to find a good example, and this seems to be the best I can find--see the comments and examples from Jonathan Drummey at the bottom:

http://community.tableau.com/ideas/2237

Best of luck!

1 of 1 people found this helpful
• 6. Re: How to calculate year on year difference without using table calculation

Attaching a version 8.1 packaged workbook example from the Idea link above.  I hope this helps!

1 of 1 people found this helpful
• 7. Re: How to calculate year on year difference without using table calculation

Finally, as a resource, I would suggest Jonathan's blog--there's all kind of tips, tricks, links, etc:

Filtering and Hiding | Drawing with Numbers

• 8. Re: How to calculate year on year difference without using table calculation

I was wondering if it is possible to use table calculation on top a table calculation.

I wanted to calculate the running total of the difference of (Current YTD and Previous Year YTD) base on date. The challenge is that date is specified by Year/Month/Day.

For example, how can we use Tableau to calculate the running total of the difference of May 1, 2014 and May 1, 2013?