How to use the value of the n-th row of a calculated column within the calculated column itself at row n+1

[Liam Borgers]

I want to create a calculated column that uses 1/6th of certain column+5/6th of the value from the previous row of the calculated column itself .

In Excel you just fix the value for the first row (e.g. equal to 10)and then from the second row onwards, you refer to the previous value of the same column.

First row: B1 = 10

Starting from second row: B2 = 1/6*(A2)+ 5/6*(B1) --> 13.8 = 1/6*11 + 5/6*10

 

 

Example

 

 

A

B

 

 

 

 

10

10

 

 

11

10.17

 

 

14

10.81

 

 

15

11.50

 

 

10

11.25

[Fabian Duerr]

Not sure if this can be done easily with a calculated column. It is pretty simple in Excel but Spotfire works differently.  It's not a spreadsheet. I think you cannot build column values that are based on the same column.

 

But you could write a simple data function that adds this column to your data table. Have you used data functions before

 

# reading data table. In this case just one column named [Value]
df <- input.table 

# initialize a vector with the values of your input column. 
# This [new.Value] is also the output parameter of this data function 
new.Value <- df$Value 

# loop thru all rows. Starting at row 2 
for (i in seq(2,length(df$Value))){ new.Value[i] <- 1/6*new.Value[i-1] + 5/6*new.Value[i] } 

# end. All values in [new.Value] are updated. [new.Value] is added as new column to the input table

 

 

 

Result:

 

 

But somehow your explenation doesn't match the calculation: Check your calculation: 13.8 = 1/6*11 + 5/6*10 ! Or I don't get your point right ;)

 

 

 

 

[Gaia Paolini]

You got different results as the formula is slightly wrong, but yes I agree a data function would easily work here. Here input.table is the data table as input (containing column A) and B is the output column to be added to the data.table

 

N < - nrow(input.table)

B