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
Answers
Questions
Solved3.32K viewsFormulas and Functions
0

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
0

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