Changing a value only for the currently selected filter category

9.59K 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,

One more question if I may. Is there an easy way for me to roll-back my upgrade from 4.0 to 4.1, or does that require a clean reinstall?

The more I think about this, the clearer it becomes to me that to accomplish what I need to with my model, I really do need access to the ‘quantrix’ property in scripting.

There are so many things I need to do which can’t be accomplished with a closed-form calculation, but only through an iterative, goal-seek like process, which to make calculation times manageable will require the ability to produce a temporary, stripped-down version of the model for these purposes.

For example, to calculate the breakeven price for a given asset (of the many hundreds in my model), with access to the quantrix property, I would do the following:
1 – save a temporary copy of the model
2 – in the temporary copy, delete all but one of the hundreds/thousands of different assets in the model, and delete all but one of the several different price decks
3 – run the goal-seek algorithm, which with literally many 10s of millions fewer cells to calculate (at the moment – probably hundreds of millions eventually) should now run very quickly, to obtain a breakeven price value for just one asset
4 – close (and if possible delete) the temporary copy of the model
5 – return the calculated value to the original copy of the model

The scripting setup in 4.1 is such an improvement in so many ways it seems sad to ask this, but I don’t see a way to do the things I need to do at the moment, other than revert to 4.0…


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

Latest Questions