Complex Formula Handling in Quantrix
I am in the process of evaluating modeling tools for modeling very large and complex set of financial algorithms. I have several general questions that I have in terms of whether Quantrix is a right tool or not. But, I would like to start with a very specific question since I like what I see with Quantrix so far!
I have two matrices one with quantity (Q) and the other with prices (P). The quantity has 3 dimensions (i, j, k) and the price has 2 dimentions (i, j). What I would like to do is calculate the Cost (C) which has a single dimensions.
I have created 3 matrices Q, P, and C and the shared categories are all linked. Is there a way to write a formula in matrix C that would give me the C(i) without creating summary items in the matrices Q and P or creating an intermediate matrix/view?
I have something like ‘= sum(Q::k) * P::k’. But, that gives me a wrong answer because it seems to sum the Q over j and k dimentions first before multiplying by first P. Is there a way of specifying the dimentional order of calculations within a formula?
The reason I am asking is I have highly defined mult-variate algebraic equations with numerous dimensions and interdependent variables. So, I would strongly prefer not to create additional variables than what I already have. For now, I am planning to craete a matrix for each variable and make the model to hold all the variables needed to calculate the charge code.
Thank you for your assistance.
Thank you, Mike.
I was hoping that there was way to do the calculations in the C table without having both i, j dimensions. It seems to me this is an issue about how Quantrix excutes the order of calculation. In other words, if the multiplications are done first, then aggregated over the unspecified dimension, we should have the right answer.
Hello – Thank you for your post:
This is an excellent question. I have demonstrated your question in the attached model.
Your ‘C’ matrix needs to have both the ‘i’ and ‘j’ dimensions (represented in the attached model as ‘Region’ and ‘Product’) in order for the formula to work as intended. Without both dimensions, the system will sum across all the dimensions as you discovered. By adding the summary item and hiding the appropriate items, you can quite quickly get to the representation you are looking for.
As you said in your post – you can also create a summary item in an intermediary matrix and use that in your formulas. Either way would work.
This is a very good use case however and one we have noted for future releases of Quantrix Modeler. Thank you for your feedback and support.