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

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Solved3.32K 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)

Selected answer as best

I’m revisiting this old post even though a solution was provided already. The problem is that the solution doesn’t seem to work anymore (I’m not sure how I got it to work before, but I think it was a different matrix structure. =/)

I’ve uploaded a new sample model with 2 matrices, CFS and CFS Sum By Yr. They share categories Cash Item and Scenario. CFS has a 2-level timeline (Yr + Qtr), and CFS Sum By Yr has a separate 1-level timeline (Yr only). I want to show the sums of each Cash Item per Year.

Using Mike’s solution from 1 year ago results in #SIZE errors. What am I doing wrong?

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

Latest Questions