Script access to cells in 3 or more dimensions

5.47K viewsScripting

I have been trying to get my head around the pipe syntax and cannot fathom how to access cells and ranges of cells in a script, particularly when more than two dimensions are involved.

One example of what I want to do is:-

I have a matrix P&L with categories:
Quarters: Qtr1,Qtr2,Qtr3,Qtr4
Items: Sales, COGS and Margin
Region: North, South

I want to be able to set Quarters Qtr2,Qtr3,Qtr4 to the Qtr1 value across all categories using a script.

Syntactically, it seemed to me I wanted to say:


But this simply sets all quarters to the North/Sales/Qtr1 value rather than the ‘relevant’ Qtr1 value.

The expression:


Works correctly, but requires repeating for each Regions/Item and necessitates change when we add new regions.

I then tried using the [b:2nizbame].each[/b:2nizbame] function to handle each region separately but cannot get around the syntax needed to access the relevant items (Sales and COGS) to do this. What I had in mind was something like:-

region:Sales:Qtr2..Qtr4|.values=region:Sales:Qtr1|.value; // wrong ... syntax error
region:COGS:Qtr2..Qtr4|.values=region:COGS:Qtr1|.value; // wrong ... syntax error

Is this the reasonable approach, and if so, how should these expressions read?

Further, ideally we would want to do another .each within the region.each to cover the item selection, e.g. region|Sales..COGS|.children.each{item-> etc. }

As a footnote, it would be very helpful if the script example Selections.model was extended to use a three or more dimensional matrix.


Hi grebe,

I am not sure but this script could help you to solve your requirements or to show you a possible direction at least:

for (def int i = 1; i<= 3; i++)
|’P&L’::Quarters|.children[i].values = |’P&L’::Qtr1|.values

You can use the attached model to test this code snippet.

Alle the best,

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

Latest Questions