copy sum of item values *by rows* from one matrix to another
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)
Hi – Not sure why I did not recommend this last time around… but since you are using timeline the inter-matrix summaries should just… work.
I did two things:
1. Linked the scenario dimension.
2. Inserted the following formula
Cash Item =sum(CFS::Quarter)
I think this get the results you are looking for? Please post if not.
Hi JLin,
I took an initial look at this. It appears that the timeline dimension found in the ‘Cash Activity’ matrix specifies a start of January 2016.
The timeline dimension found in ‘Cash Activity by Year’ does not specify a start date.
You can configure a timeline by right clicking on the dimension tile, and choosing Timeline > Configure from the context menu.
If you specify a start period for the ‘Cash Activity by Year’ timeline dimension the errors go away and the formula returns value. I admit that the message of ‘can’t figure out what to do with timelines’ is not super helpful, but I think the formula does not know where to put the values since only one of the categories specifies a start period.
Hopefully this helps?
Mike,
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.
Mike,
Based on your suggestion I’m able to "fix" my model so it doesn’t have that error anymore. My year-only Timeline (Cash Activity By Yr matrix) was configured to be 14 years, with no starting date, while the source matrix (year + quarter) only had 10 years and had a starting date of Jan 2016. I merely changed the year-only Timeline to 10 years, and the error went away.
But try as I might, I’m unable to "break" the sample model you’ve provided, even though I’ve configured the two timelines identically to my own model.
Please give me the FTP details so I can upload my model.
Hi JLin – sorry to hear about the formula / timeline troubles. I did go back to your earlier post / model and opened / saved in version 5.3 and it appears to work correctly. There must be something unique in the model you are working with. Couple of thoughts.
1. I have attached the model to that post saved in version 5.3 – any chance you can ‘break’ it to get that message?
2. One of the reasons you may get that message is that the two timelines in your model don’t overlap. We made some code changes in this area to not be overly aggressive in the way we handle recursion in timeline categories. Check the start dates of each timeline to see via right click the dimension – Timeline > Configure.
3. We have an FTP site that you can upload your model too. I can have engineering take a look if you want to supply the model to us: Shoot an email to customerservice@quantrix.com and we can supply the upload URL if you decide to go this route.
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?
Hi,
I am very new to Quantrix and have a similar problem.I have several matrices that result in "net incomes" for different scenarios. I created a summary matrix and used the formula "x net income = x matrix::net income row". This returned only the year 1 value across the row rather than the year 1 to year 10 values in the "x" matrix. How do I get the formula to return the year 1 value and then the year 2 value and so on?
Thanks, Tim
FYI for others’ benefits – I tried another test where I attempted the same formula with a target matrix that did not have a timeline defined, just a "normal" category with items Y1-Y5. It ended up incorrectly copying the first year’s sum across Y1-Y5.
I doubt I could have thought of Mike’s solution on my own since I kept thinking that timelines only act "smart" on matrices that share a common timeline, e.g. all the slicing and dicing capability required a common timeline. Apparently they are auto-magical enough that they "just know about time" even when linking across different, unlinked timelines.
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?