copy sum of item values *by rows* from one matrix to another

Solved11.99K viewsFormulas and Functions

In order to work around the limitation of valueat() not working properly if a matrix has a timeline which contains summary items, I have revised my models so that none of my matrices with linked timelines contain summary items.

Now, for presentation purposes, I would like to create Quarterly and Yearly summary versions of many of my matrices, for instance Income Statement, Balance Sheet, Cash Flow Statement. I have not been able to find a way to copy the summed items from the original Matrices into the summary-enabled copies, other than typing the same repetitive formula once for each and every item that I want to copy over. Is there an easier way to do this????

Please see attached. Source Matrix represents the original matrix, with a 3-level Timeline and no summary items in the timeline.
Summary Matrix is the target matrix where I want to display the sums for EACH item in IS Item, summarized by Quarter and Year.

I am able to copy and sum the values for a single item such as Product A, using the formula: [code:1w07b98g]Product A = sum(Source Matrix::Product A)[/code:1w07b98g]. I would like to avoid having to type that same formula over and over again for each item in IS Item. Ideally I could just type [code:1w07b98g]IS Item = sum(Source Matrix::IS Item)[/code:1w07b98g] but that gives the wrong results; every row ends up with the same values.

Is there any way to achieve my goal? And is there any time frame for when this bug with valueat()+timeline+summary items will be fixed, so that I won’t have to do any of this? (Please note: Even if the valueat() bug is fixed I still believe this ability to bulk copy a 2-D range of values on a [b:1w07b98g]row by row[/b:1w07b98g] basis will be quite useful and make for more concise formulas)


I didn’t manage to break your sample file, but I finally isolated the bug again in my own file. I’ve cut the file down in size for clarity and uploaded it to the URL you provided.

This time, I tried to change the "summary" timeline to 10 years (from 14) in order to match the "detail" timeline from which I was copying sum values. Unlike the last time I tried, this time the error did not go away.

I appreciate your help.

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