0

I am trying to do a cumulative product between Column1 and the previous row of Column2 in Spotfire. As in the Table below, I need Column2 to be the product of DataColumn1 and the previous value of CalculatedColumn2. Column2 is the calculated column I am trying to create.

I am stuck because there is obviously a cyclical dependency if using a Spotfire calculated column. For this reason I have no code or error to share, I know a simple calculated column will not work. I think I need to use custom expression functions or some sort of R code in order to do it but have never worked in R and need help!

This question and answer linked here are exactly what I am trying to do. The answer to this question has a code that worked for this person, but how do I use that code in a Spotfire calculated column and enter my corresponding columns?

DataColumnA CalculatedColumnB
6 6
8 48
9 432
2 864
4 3456
6 20736

Any help is very appreciated! Thank you!

TMaxwell
  • 1
  • 1
  • You could just use a for loop for this: `for (i in seq.default(2, nrow(df))) { df[[i, 2]] <- df[[i, 1]] * df[[(i - 1), 2]] }`. This would go from the second to the last row and multiplies for each iteration the vlaue of column 1 in the i-th row with the value of column 2 in the (i-1)-th row and assign it as the value of column 2 in the i-th row. – FactOREO Jul 28 '23 at 19:37
  • Thank you for the information, would I use this code in the calculated column expression or in a custom expression function? – TMaxwell Jul 28 '23 at 21:05
  • 1
    Please read about [how to make a great R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and update your question accordingly. Include a sample of your data by pasting the output of `dput()` into your post or `dput(head())` if you have a large data frame. Also include code you have tried, any relevant errors, and expected output. If you cannot post your data, then please post code for creating representative data. Please do not post pictures of code and data. – LMc Jul 28 '23 at 21:06
  • I have edited the post as best as I can. – TMaxwell Jul 28 '23 at 21:19

2 Answers2

0

You are looking for the function cumprod:

library(dplyr)

df |>
  mutate(CalculatedColumnB2 = cumprod(DataColumnA))

In base R

df$CalculatedColumnB2 <- Reduce(`*`, df$DataColumnA, accumulate = T)

Output

  DataColumnA CalculatedColumnB CalculatedColumnB2
1           6                 6                  6
2           8                48                 48
3           9               432                432
4           2               864                864
5           4              3456               3456
6           6             20736              20736

Data

df <- structure(list(DataColumnA = c(6L, 8L, 9L, 2L, 4L, 6L), CalculatedColumnB = c(6L, 
48L, 432L, 864L, 3456L, 20736L)), class = "data.frame", row.names = c(NA, 
-6L))
LMc
  • 12,577
  • 3
  • 31
  • 43
  • Thank you for your answer! Where/how do I input this function in Spotfire? – TMaxwell Jul 31 '23 at 13:36
  • @TMaxwell You are welcome! I am not familiar with Spotfire, but these [instructions](https://www.tibco.com/blog/2014/12/08/add-your-own-functions-using-r/) seems straightforward. – LMc Jul 31 '23 at 15:02
0

I am not sure I fully understand the columns involved, as the image does not fully match the description. If CalculatedColumnB from the image is all you need, from DataColumnA, then you can do it without involving R code:

1 - create a calculated column [RowID] to give you a row id (if you don't already have one) as:

RowId()

2 - create your cumulative product column as:

Product([DataColumnA]) over (AllPrevious([RowID]))

If you have extra columns you need to group by, you can add an Intersect(..) e.g.

Product([DataColumnA]) over Intersect(AllPrevious([RowID]),[GroupColumn])
Gaia Paolini
  • 1,044
  • 1
  • 5
  • 4
  • Thank you for your input. The problem becomes I need to multiply the value in DataColumnA with the previous value of CalculatedColumnB, in CalculatedColumnB. So one of the values in the equation is produced by the same calculated column, in the previous row. – TMaxwell Aug 03 '23 at 16:39
  • Not sure I understand your goal then. – Gaia Paolini Aug 04 '23 at 07:53