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

For example:

From the attached dxp, We need to calculate rest of the values in Column "Value" for every next year.

second value formula should be: first value in the Value column * percentage 

 

2 Comments

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

itsmezakky - Jun 14, 2021 - 3:18am

The dxp does not seem to be attached. Could you please attach it. 

shaggarw - Jun 11, 2021 - 11:37am
+ Add a Comment

(1) Answer

Login