Liam Borgers Posted November 9, 2020 Share Posted November 9, 2020 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 Link to comment Share on other sites More sharing options...
Fabian Duerr Posted November 9, 2020 Share Posted November 9, 2020 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 ;) Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 10, 2020 Share Posted November 10, 2020 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now