Adding the value of a previous row to a current cum calculation results in "invalid expression"

[Anthony Quarm 5]

I dealt with this previously, however the solution at that time was to rewrite the entire equation so that it was no longer dependent on the other column. While a resdeint genius here was capable of that, I don't beleve I am quite that clever. I did however learn a few tricks from that incident that has helped me avoid several issues since. Such as breaking down my equations into amsaller calculated chunks.

 

The cumulative equation I was given from excel converts to this for spotfire:

 

IF([Decline.month]*([initial rate]/[QiCalc])^${Oilb2}>=[Dm.month]

Then

([QiCalc]/([Decline.month]*([badjusted]-1)))*(((1+[badjusted]*[Decline.month]*[Days conv])^(1-(1/[badjusted])))-1),

else

([initial rate]/[Dm.month])*(1-(EXP(-[Dm.month]*([DaysDiff]-0))))+PreviousRowCum

 

The bold part is where it all falls apart. I have both of those equations set as their own calculated column. Which I then call:

IF([Decline.month]*([initial rate]/[QiCalc])^${Oilb2}>=[Dm.month],[CumCalc1],[CumCalc2])

CumCalc1 does great. Cum Calc 2 works great as long as I don't try to add it to the last rows cum

 

I created a calculated column called CumProdLast

First([Cum Prod]) OVER (Previous([RowID]))

This accurately places the value of the last rows cumulative production on this row. So, the simple solution then would be to add it...

([initial rate]/[Dm.month])*(1-(EXP(-[Dm.month]*([DaysDiff]-0))))+[CumProdLast]

Unfortunately, this guves me the same "invalid expression".

 

I would really appreciate any input on how I can accomplish this. I feel like I am incredibly close to finishing this project, but these 'simple' calls to previous rows are killing me.

[Fabian Duerr]

I remember your previous problem... ;) And if you learned something from the solution I'm happy!

 

So now it might be time to learn even more. Something that also would have helped you in the previous problem. 

 

You can solve this using a data function to create you new column. Please see this post to learn how to do that. Check my post in the comments and Gaia's post in the answer section. Both show the same approach:

 

https://community.spotfire.com/questions/how-use-value-n-th-row-calculated-column-within-calculated-column-itself-row-n1

 

Applying custom data functions will bring you to a new level in solving such task. Let me know when you have questions here

[Anthony Quarm 5]

.

[Anthony Quarm 5]

I understand the concept of a data function after reading your comments there. However I'm confused where to start learning the syntax for a data function..For example, the very first line has it defining the input table... 

 

I've tried setting up the following:

# reading data table. In this case just one column named [Value]

df <- CumCalc2.EurSheet 

# initialize a vector with the values of your input column. 

# This [Cum.Value] is also the output parameter of this data function 

Cum.Value <- df$Value 

 

However even a very basic attempt to just add 1 to it results in nothing when I run it. So I assume my syntax is incorrect... 

[Fabian Duerr]

You can check some good videos on YouTube for how to use data functions in Spotfire.

 

You might want to have a look at some basic R programming tutorials as well. I had zero R skills one year ago but with some Python knowledge it was easy to understand and to learn. The internet is full of free learning materials and so far I found a good answer to any question I had by just using google.

 

When you use a data function in Spotfire you have to define your input and your output. The input can be your data table from Spotfire 'EurSheet'. But the variable name for this can differ. You can simply call it 'df' (soth for data frame) or whatever you like. In your case the output 'CumCalc2' would by of type column, as you want to add a nwe column.

 

So please check out some video tutorials and let me know how to get along with that.

[Anthony Quarm 5]

So is this something that can be called "in-line" automatically Or does it have to be called by pushing a buttonI believe I can figure out the code I need to do the math properly, but I need help getting the inputs set up. Considering my equation references 4 or 5 different calculated fields... how do I read all of these into the data function for processing Unfortunately the videos I find are incredibly generic, and don't cover this sort of thing. 

 

I also am getting info from people telling me it can only be called by pushing a button, which would be a deal breaker for something like this. So, if that is not necessary, then can you point me to a resource that explains how a data function can be called directly during calculationsThank you very much

[Fabian Duerr]

Data functions can run automatically, on-demand and they can be triggered with scripts.

 

I will attach a short video here how you can write your 1st very simple function. This function will reproduce your input, which is a complete data table here with all columns. So the output will also be a table.

 

name your function

write your function

define your input variable (name + type)

define your output variable (name + type)

run your function: say what your input is and what your output is

exit the menu without saving the function to the library. It will be embedded in the analysis

check the output by opening the new table

decide if this function shall run on demand or automatically

 

I can continue with more short videos tomorrow if this is helpful for you.

 

/modules/file/icons/video-x-generic.png 1st-function.mp4

[Anthony Quarm 5]

Thank you. The video was helpful, however it is very difficult to make out. My primary concern is that I am making such little progress each day that those I am working for may just decide it isn't worth the amount of time it is taking and scrap it.

 

At this point, the issue is I can read a table and output a table, but I don't know how to manipulate a single value from the table. I still don't understand the syntax of how things are written, and I have yet to find any documentation on this.  

 

So, if I have two calculated values, and I need to process them in an iff statement:

eg: if X is true, use calc1. If X is false, use calc1 + calc2. 

 

I'm not sure how to do this. I suppose the frustrating part is how simple this is in other things I know how to use, like vbscript or excel.

[Fabian Duerr]

Please check my dxp file and see the details for the data function to get a better understanding how to solve your problem. The input is a data table with 3 columns and the output adds a new column based on some conditions.

You'll see how to address a column and a value from a specific row in this column.

I also included a LOOP and an IF statement.

That should be all you need.

[Fabian Duerr]

When I 1st came in touch with R I just had some basic knowledge of Python. But with that knowledge, I was able to learn R pretty fast.

 

There are probably plenty of great online resources like Coursera, Codecademy, Udemy to learn R. And a lot of forums with helpful discussions.

 

Depending on your Spotfire version you might just want to learn Python, which I think is supported from Spotfire 10 on. The reason why I suggest Python is just because it's much easier to find great free online tutorials. 

[Anthony Quarm 5]

thank you for taking the time to do this. Unfortunately when I discovered my previous values were incorrect, it made it impossible to go forward with this section of the project.

 

I'd like to try to use a data function to solve my other issue (the cyclical dependence error when two columns calculate based on each other).Unfortunately, that seems to mean using r script to do the calculations, which are fairly complex. And so far the only r script examples I can find are very simple if X then assign Y examples, rather than conditional algebra. Do you have any suggestions where I might be able to find that information thank you.

[Anthony Quarm 5]

Unfortunately learning a language to solve this particular project is something that is unlikely. This isn't my typical work, but I'm doing my best to help with it as there is no one else here who knows anything about Spotfire.

That said, I suppose I need a data function that calculates the Oil Rate At Time and the Oil Initial Rate simultaneously As they are dependent on eachother. Unless a calculated field can reference itself As in, the Oil Initial Rate is always the Previous Oil Rate At Time (save for the first iteration). Is it possible to write the Oil Rate At Time to use the previous rowid of itself in the calculation of the current row Or does that still violate the logic of the way spotfire processes data With two different columns, I could see how one column may not have been generated yet. But I would assume the previous value of the existing column should already exist