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
Solved4.46K 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 an old thread because now that I’ve upgraded to Quantrix, your last solution to me has stopped working.

In a matrix Cash Activity By Yr, I have a formula: [code:23lx2le9]Cash KPI = sum(Cash Activity::Quarter)[/code:23lx2le9]. The matrices [i:23lx2le9]Cash Activity By Yr[/i:23lx2le9] and [i:23lx2le9]Cash Activity[/i:23lx2le9] share the same categories except for having different timelines. In Quantrix 5.1.2 that formula worked exactly as you suggested. But after upgrading to 5.3, I now get an error:
[code:23lx2le9]Unable to figure out what to do with the timelines[/code:23lx2le9].

Unfortunately my model is 95 MB so it’s not practical to upload an example. Have you seen this error and how might I go about resolving it?

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