1 Reply Latest reply on Mar 24, 2016 7:23 AM by John Sobczak

    Calculate a field based on a previous year's data

    Harnish Patel

      Hello all,

       

      How can I calculate a field for a certain year using data from a previous year.

       

      I want to Calculate 2015 and 2016 Revenue as a Percent of 2014 Expenses.

       

      Thank you!

        • 1. Re: Calculate a field based on a previous year's data
          John Sobczak

          Its always best to provide sample workbooks for these type of questions.  Without a sample workbook I can speculate you can create calculated fields as follows:

           

          2015 Revenue:  If [Year] =2015 then [Revenue] end

          2016 Revenue:  If [Year]=2016 then [Revenue] end

          2014 Expenses: If [Year]=2014 then [Expenses] end

           

          Revenue % of Expense=   (zn(sum([2015 Revenue])) + zn(sum([2015 Revenue])))/zn(sum([2014 Expense]))

          1 of 1 people found this helpful