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

Mike,
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.

Latest Questions