How to calculate a column value based on the previous value of the same column?
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
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|
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|
Please let me know if you need any additional information.
The dxp does not seem to be attached. Could you please attach it.