Referencing Value from previous Row

[Anthony Quarm 5]

I'm still wrapping my head around how spotfire calls up data from previous rows (similar to how an excel spreadsheet can reference any cell).

I am creating a calculated value. Lets call it InitialRate. That value is then used to calculate a second value, RateOverTime.For the first row, InitialRate is equal to a user input variable. Then RateOverTime is calculated based on this. No problem so far.

For the second row, InitialRate should equal to the value of RateOverTime from the first row

For the third row, InitialRate should equal to the value of RateOverTime from the second row

As I have RowID's calculated for each row, I started with a fairly simple:

If([RowID]=1,${UserInputField},0)

This properly brought in the user value for the first row and led to a proper calculation of the RateOverTime field for row 1. And for row's that were not the first one, it wrote a 0 to the line for now.

Separatly, I began testing out how to grab the value of a previous row and bring it into the value of a current row (but a different field). I used:

(First([RateOverTimeV) OVER(Previous([RowID]))) and assigned this expression to a test column. It properly grabs the value of RateOverTime from row 1 and writes it to the test value on row 2.

So... thinking I had solved the problem, I did the next logical thing and tried to insert that statment '(First([RateOverTime]) OVER(Previous([RowID])))' into the If statement, as the false section.

At this point the preview shows it behaving properly for one iteration.

Row 1 properly grabs the user input value. Then it properly calculates the RateOverTime value.

Row 2 properly grabs the RateOverTime value from row1 and inserts it as the next InitialRate in row 2. However the RateOverTime in row 2 is not calculated using this new number. It simply stays at 0. Granted, this is all in the preview window. If I tri to actually hit "ok" I am told it is an invalid expression.

 

Eventually, I landed upon this.

If([RowID]=1,${UserInputField},(Sum([RateOverTime]) over (Previous([Month]))))

I get the same error that it is not a valid expression. The false argument (Sum([RateOverTime]) over (Previous([Month]))) is the exact example given to do this type of method over on this blog post:

https://www.bigmountainanalytics.com/using-node-navigation-in-spotfire/

Unfortunately, for my data it doesn't work. And my guess is it has something to do with spotfire calculating values by going down instead of over. It can not calculate the next InitRate without having calculated the last RateOverTime. So if it tried to calculate every InitRate first, then that is just going to cause problems as the values for RateOverTime don't exist yet. So it is presumably getting stuck in a logic loop. The solution would be to force it to process one row at a time before moving to the next. Is this possible

 

All of the values above are real numbers, so it's not an issue with data types (I don't think anyways).

 

The silly thing, is over in excel this is as simple as referencing the cell one row up and one column to the left. And this can be done with a direct link to the cell or mathamatically. I hope I am simply missing a simple approach to this.

 

Thank you for reading and any potential help anyone can offer.

[Fabian Duerr]

How did you insert this statement into your IF condition How do you calculate RateOverTime

 

Can you drop a small sample data set (excel, csv, dxp) with expected result here

[Anthony Quarm 5]

Unfortunately it is fairly complex and takes into account a half dozen or more inputs and previously calculated fields.

 

RateOverTime (which is actually called RateTimeVM in my data) is calculated in spotfire using the following equation:

If([DeclineNomM]*Power(([initRateVM]/${InitQI}),${Oilb2})>=[DMNM],${InitQI}*(Power((1+[AdjustedB]*[DeclineNomM]*[DaysConvStd]),(-1/[AdjustedB]))),[initRateVM]*Exp(-[DMNM]))

 

DeclineNomM = [DeclineNomYr] / 12

DeclineNomYr = -ln(1-${OilDi2})

OilDi2 = User Input

DMNM = [DMNY] / 12

DMNY = -LN(1-${DM})

DM = User Input

InitQI = User Input

AdjustedB = User Input

DaysConvStd = [DaysFromInit] / (365.25 / 12)

DaysFromInit = Sum([DaysM]) over (AllPrevious([RowID]))

DaysM = A calculation of the exact number of days in that month.

 

Essentially, it is rebuilding this spreadsheet in spotfire

http://s000.tinyupload.com/index.phpfile_id=06350190904144599970

[Fabian Duerr]

It's okay when your file is complex, but can't you just create a simplified version It will be important to understand how all this work should work.

 

But so many things can go wrong here. For example your data type could be wrong. Is your [Month] column really a 'date' Is your user input field a 'numeric' Do you understand what your IF statement is really doing

 

Sometimes it is really helpful to create a small data set where you can understand all calculation and where you know the expected output. I mean all you need are probably 3-4 columns (RowID, Date/Month, Rate), a few rows and a user input field. You can add more complexity later on.

 

If you provide such a file (excel is fine) I can try to help further. (For security reasons I can't open the link to the spread sheet)

[Anthony Quarm 5]

Ok, sure, but if you can't open that link, how can you open any other link I send you I mean, the entire thing is based on calculated values. So providing enough data to populate one row is the same as provding enough data to populate the entire spreadsheet.  What is the best way for me to provide that to you

[Fabian Duerr]

You can just create a sample data set from scratch with whatever values.

 

Or you (hard) copy a part of your data to a excel sheet and upload it to your post. Please limit it to the relevant columns and maybe 50-100 rows.

[Anthony Quarm 5]

Attached is the excel file I had linked earlier. Apologies, I didn't see the file attach button *below* the text box. I was searching in the header bar for a place to link files.

[Fabian Duerr]

Okay. Thanks for the file. I already had a look at it and I think this should not be a big problem to be solved. I'll probably find some time on Sun or Mon to reply

[Fabian Duerr]

You cannot use the same approach as in excel because it would cause cyclic dependence in Spotfire.

But things are getting pretty simple when you make the right choice for your [Days conv] column. You'll see in my solution. Just checkout all the document and column properties and let me know when you have questions.

PS: One thing a came across in Spotfire (unfortunatelly not for the first time) is that user input fields (as a document property) are somewhat useless when the input is a date. Because Spotfire somehow doesn't treat it as a date (even though data type is DATE) when you use it in a calcuated coloumn. So you should use a string as user input and convert it as a date.

[Anthony Quarm 5]

@fabd, unfortunately I have realized that the way you have rewritten the formula only works as long as the DM value never changes. In testing with all user inputs, I found that changing the DM does not have the intended effect on the outputs.

[Fabian Duerr]

Are you sure

 

It seems to work fine on my side. Please ensure that the input can only be between 0 (=0%) and 1 (=100%). It's probably better to use a range slider rather than a text input field.