4 Replies Latest reply: Mar 3, 2017 10:28 AM by Branislav Bujnak RSS

    Getting Year-Over-Year Results

      I have a table with revenue amounts for a given year, month and category.  Each record contains the year, month, category and amount. 

       

      RevYearRevMonthRevCategoryRevAmount
      2011JanPaper35000
      2010JanPaper30000
      2011FebPaper26000
      2010FebPaper28000
      2011MarPaper16000
      2010MarPaper15000

       

      My sheet has a selection box showing the years in the table and used the field RevYear.  In my pivot table I want to be able to show an additional column with the previous year from the one selected and also a column for the year-over-year variance.

       

      With the year 2011 selected:

       

      For the column label I used the expression (it returned 2011):     RevYear

      I used this formula to show the selected year (this returned 77000):  sum(if(RevYear),RevAmount,0) 

       

      For the previous year column label I used the expression (it returned 2010):      RevYear-1

      For the previous year column I used (this also returned 77000, it should have returned 73000):   sum(if(RevYear-1),RevAmount,0)

       

      Does anyone have any idea what I am doing wrong?

      Thanks in advance for your help.

       

      -Rick