2 Replies Latest reply on Dec 13, 2012 10:54 AM by Ryan hollamby

    Percent Change Calculate- Restarting calculation with each state

    Ryan hollamby

      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.

       

       

      data.png