The issue that I am having is calculating percent change over 3 and 5 years. The image below is in excel to just show you the calculation I am trying to complete and the data layout. As you can see, as I switch states (i.e.row 22) the calculation needs to stop. So year to year change I just did in excel, then deleted the switching cell and it kept all the formulas in line (couldn't figure out how to do it in Tableau easily). I would like to make a calculated field to solve my 3 and 5 year change.
I want to take the most recent 3 years ( 2010, 2009, 2008) and 5 years (2010, 2009, 2008, 2007, 2006) for each state and compare it to that states 2011 crime rate.
Any help? I know there will be a lot of blanks but I will just exclude the nulls, not an issue. I can do this in excel but going to be sort of a pain. Hoping tableau could save me some time.
Hi Ryan,
When posting a question, please post some sample data or, better yet, a packaged workbook. That makes it a lot easier for us to answer your question with your data, and get you an answer that will work for you.
I set up an example of this in the attached using Superstore Sales data. I used SUM(Sales) as a stand-in for the crime rate, Quarter as a stand-in for year (so there were enough data points), and State. I set up a variation of a moving average table calculation to generate the prior 3 Quarter and 5 Quarter averages, then used a basic % difference calc go get the change. The Compute Using for all of the table calculations is the Order Date.
Jonathan
