1 Reply Latest reply on Dec 20, 2016 4:35 PM by diego.medrano

    Subtracting Previous Year's Value from Current Value in a calculated field and filtering (not table calc)

    Seth Ramey

      Alright I have a tricky one.   I have a database that tracks how much savings each of my departments earned each month.  For one (complicated) department, Department A, to get the value of their savings we subtract the amount that they saved last year from the amount that they saved this year in the same given month (so we subtract march 2014's savings from march 2015's savings to get the adjusted value of march 2015 savings).  For all other departments, their savings for March 2015 is just the amount that they saved in March 2015.

       

      Big issue: all dates are in one column.  All savings are in another column.  So I can't just subtract the value in the 2015 column from the value in the 2014 column.

       

      I want to be able to build a stacked bar chart that shows our company's total amount of savings for each time period broken out by department.  I assume I need to build a calculated field that has an IF statement that, if, <department>= Department A calculates Department A's adjusted savings (march 2015-march 2014), and otherwise gives the value of savings in March 2015.  Any ideas how to write this or any other ways of solving it?

       

      I assume I'm going to need to create a calculated field to create the previous year's value and only use it if it is a Department A savings.