Changing a value only for the currently selected filter category

7.10K viewsScripting

Hi – a question that has some similarities to one asked in the previous post “Addressing cells programmatically”.

I am writing a simple script to replicate an excel-style goal-seek functionality. The script is to work on a matrix with a filter category (‘PhaseName’). The idea is to change the value of a particular item ‘Decline Rate’ (incrementing it up or down), depending on the value returned by the model. Only the value of ‘decline rate’ for the ‘PhaseName’ currently selected in the filter should be changed, however.

I can use the .itemIndex property of the .getFilterItem method to obtain the index number of the currently selected ‘PhaseName’, and can get the particular value for the ‘Decline Rate’ of the currently selected ‘PhaseName’ as follows:

int current = |Phase Details|.getFilterItem(|Phase Details::PhaseName|).itemIndex

|Phase Details::Combined Peak.Decline Rate|.values[current].value


However, it appears that using values with an index number in this way is a read-only property – one can’t set this to a new value.

In the earlier post “Addressing cells programmatically”, I found a useful code snippet from dom, achieving something similar by creating an ArrayList based on all of the different values of the item being changed, changing only that value corresponding to the currently selected filter category, and then writing the ArrayList back to the item. In my case, this would work as follows:

def ArrayList<Item> declines = |Phase Details::Combined Peak.Decline Rate|.values

declines.set(current, |Phase Details::Combined Peak.Decline Rate|.values[current].value-increment)

|Phase Details::Combined Peak.Decline Rate|.values = declines

This does in fact work – but it seems an inordinately complicated way of doing something that users must surely want to do all the time. Is anyone able to suggest a better way of doing this?

On a related note, since when the model is complete, the filter category “PhaseName” will have several hundred ‘phases’ in it, I have a related question on the model.ensureCalculated() method. With only a few phases, calculation times for this model are generally under a second – so if the goal-seek algorithm I have put together takes 6-10 iterations to find an answer, and must calcuate each time, while it is slow, it is not unbearable. Once several hundered phases have been added to the model, a full recalc will almost certainly take well over 10 seconds, meaning 6-10 iterations will become painfully slow – frustrating since in this case, only one of those several hundred phases actually needs to be calculated. Is there any method that can be called that will calculate the model only for the currently selected phase, rather than for all of them? This would seem like an essential ability for anyone with a large, complex model…

Many thanks.


Hi Ben,

Many thanks – I really do appreciate the offer. While I would certainly love the feedback, and would be very happy to share the model I am working on with you and the team at Quantrix, and even with a few other forum members, since what I’m developing is ultimately for commercial use, I’m not sure I can make it completely publicly available by posting it online! Are there any other ways by which sharing might be accomplished, or does that undermine the fundamental spirit of openness of the forum?

To talk in more general terms, though, while there may be optimizations that could improve the speed of the model, ultimatley I am building an oil and gas portfolio model which takes well-by-well level inputs (say up to 40 for any given field) to calculate costs and production, runs those inputs through a highly configurable economic model capable of broadly replicating the economics of almost any hydrocarbons fiscal regime in any country in the world, and returns the cashflow results. The idea is to be able to do this for hundreds of different fields, in scores of different countries (each with different fiscal terms), and get the results back at multiple different price decks and different sensitivities to start dates and capex amounts, and also at the level of both individual fields and entire company or country portfolios. Needless to say, it’s a pretty complex model, and all the optimization in the would only reduce its calculation time so far!

Since to run a goal-seek requires iteration (usually 6-12 full recalcs to end up with the correct answer), inevitably with a model this complex, that is going to take a long time – and longer and longer as more and more oil fields are added to the model. There may well be a more elegant solution to the problem than the brute force of a goal-seek, like something using differential calculus for instance, but I haven’t had a chance to get my not-brilliantly-mathematical brain around that possibility yet. Regardless, it does seem to me that for any complex model, there will be times that one wants to script a calculation without having to wait for a full recalc of the entire model. If that could be done directly by specifying a range to recalc, that would be fantastic. If not, then the ability to simply open a new model, add a few matrices, ranges and formulae programatically, calculate the results, and then close the model and return to the main model would be a very useable alternative. But I do think something is needed to enable just a small subset of calculations to be run, without a full recalc.


You are viewing 1 out of 14 answers, click here to view all answers.