Quantrix Evaluation:Strange behaviour when building Intermatrix links

4.00K viewsFormulas and Functions
0

Dear Quantrix staff,

as you know, I am currently evaluating Quantrix Modeller. Unfortunately I am experiencing a strange behaviour when I build a very simple model on my own. The surprising thing is that I get the same problem when trying to rebuilt some of the sample models such as the tutorial.model file. What happens:

In a first step I try to build a very simple sales model with two years and two products. I build totals for the products and totals for the years. Works fine as everybody would expect.

In a second step I build a second matrix where I intend to “consolidate” the values for products in the following way. I still have the years in the colums, but in the rows I have now the financial statement items such as revenues. I want to transfer from the first matrix the totals of revenues for all products per year. In other words: I do not differentiate any longer between products. I intend only to take over the sum of revenue of both products. This is what you alway show in your examples, demos, webcasts and so on. I find this logic in the samples and I apply the same formula.

But surprisingly the following happens now. The Modeler takes over the value for the first year and puts the same value in all the other years… I had expected that he takes over the aggregated values (both products) but still differentiated per year. I tried several formulas as you can see in the attached file but whatever I do, the values for the different years are wrong.

I am trying to find out my mistake since a couple of days now, but whatever I do, I do not find a solution.

Even much more strange… Today I had the idea to take one of your sample models delivered with Quantrix and to rebuild some parts of it in the same model where I tried to build my own example. It was surprising that I encountered then the same problems although I really copied your logic. What happens here?

I attach the file with my sample. Do not be surprised: You will find two matrices build on my own (named as Sales and Consolidation) and you will also find the matrices from the “tutorial.model” sample file (P&L and sales projection (000) where I tried to rebuild the logic manually. If you have a look at these two matrices also you will find the same strange error also, i.e. the value for Revenues Budget in the first year is the same as in the other years. The transfer of the sales to the P&L does not work properly although the formulas are absolutely identical with the formulas used in the master model…

I was already thinking about the possibility that it might be a language problem of my laptop which makes the formulas not to work properly. Therefore I uninstalled the Quantrix version with the german language files you had sent to me and which I had installed and reinstalled the english version. But the problem still occurs… Strange.

Any help would be highly appreciated.

Thanks in advance,

Best regards,

Joachim

0

Hi – thanks for the follow up. When building a model the link of categories should happen naturally. In my original post I said:

“By default, a copy and paste of a category will establish a link. Also, you can click and drag a category from one matrix to a new matrix to establish a linked category.”

So if you copy and paste a category between matrices in the same model they should be linked by default. Also when dragging a category from one matrix to another in the same model they will also be linked by default.

If the application is not working this way for you please let me know.

-Mike

0

Hi Mike,

thanks for your valuable help. Now I can see the mistake I made. And now where I am playing around with the linked categories I can see the power of it. Really great.

Initially, when I read in your reply that I have to link the categories manually I was first a bit disappointed because I thought once I defined such a category the modeler should know by itself that there is such a category and use it (i.e. link it) automatically. In other words: Being a bit more intelligent. 😉

But I guess (still have to think about it more in detail) that there are some good reasons why it should not work this way automatically. And admittedly even planning systems such as SAP BI IP do not link all the categories by default in a new matrix. You need to define it manually.

So, for the time being it looks promising and I can proceed to test Quantrix Modeler by building all the models which are described in the K.Scott Procter´s book “Building Financial Models with Microsoft Excel” :D:D:D

Don’t be shocked now. I just intend to compare the way doing this modeling by using both tools and to find out which way is more efficient and especially which way is easier. I guess I know already the answer but I want to measure it a bit more objectively. :D

Thanks again for your help which I appreciated very much.

Best regards,

Josh

0

Hi – Thank for posting.

What needs to happen in your model is to link the similar dimensions in your model. Once linked, the formulas you have written will work as designed. Linked categories allow Quantrix to rendezvous the items in the dimension to put things in the proper spot.

In your example, you have two different types of Year dimensions, so I made a Year1 and Year2 and linked accordingly.

By default, a copy and paste of a category will establish a link. Also, you can click and drag a category from one matrix to a new matrix to establish a linked category.

You can learn more by searching “link category” in the application help. (Help > Search).

Please see the attached model file. Feel free to post again with any questions.

-Mike

Latest Questions