Jump to content

How to calculate a column value based on the previous value of the same column

Spoorti K

Recommended Posts

Hi Shaggarw,Please find the attached dxp file. I have written the "Calc" column in Spotfire but the values are getting calculated from the starting value (3000) not from the previous rows.Sorry I am unable to attach the dxp file. Hence attaching the calculation i did in excel and the calculation i have written in Spotfire

Year Percentage Value-Manually calculated in excel Calculation in Excel
2011   3,000  
2012 8% 3,240 `=C2*(1+B3)
2013 7% 3,467 `=C3*(1+B4)
2014 10% 3,813 `=C4*(1+B5)
2015 5% 4,004  
Spotfire Calc - Created a Row ID column and referencing, trying to refrecing it to previous row but it calculates only based on the starting plan.case  when [Year]=2011 then [base Value] else 

Avg([base Value]) * Sum(1 + [Percentage]) OVER (Previous([RowId])) END

Year Percentage Base Value Value-Manually calculated in excel Calc RowId
2011   3,000 3000 3000 1
2012 8% 3,000 3240   2
2013 7% 3,000 3466.8 3240 3
2014 10% 3,000 3813.48 3210 4
2015 5% 3,000 4004.154 3300 5
Please let me know if you need any additional information.Thanks
Link to comment
Share on other sites

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...