4 Replies Latest reply: Feb 26, 2015 3:05 PM by Gysbert Wassenaar RSS

    Using script to calculate the difference between two rows

    Yvonne Han

      Hi,

       

      I'm building a financial dashboard that needs to show variance between budget and estimate for each product each month, and want to do the calculation in script.

       

      My file looks like this

      Product Year Month Type Amount
      Product 1 2015 Jan Bud                         10,000
      Product 1 2015 Jan Est                           9,000
      Product 1 2014 Dec Bud                         10,000
      Product 1 2014 Dec Est                           8,000
      Product 2 2015 Jan Bud                         10,000
      Product 2 2015 Jan Est                           7,000
      Product 2 2014 Dec Bud                         10,000
      Product 2 2014 Dec Est                           6,000

       

      I want the another table look like this

      Product Year Month Type Amount
      Product 1 2015 Jan Var                         (1,000)
      Product 1 2014 Dec Var                         (2,000)
      Product 2 2015 Jan Var                         (3,000)
      Product 2 2014 Dec Var                         (4,000)

       

      Does anyone know how to write a script for that?

       

      Thanks

       

      Zixiao