Using ‘[THIS + Variable]’
Hi all,
Not sure if I am missing something obvious here but i would like to use a recurrence formula where the ‘offset’ is determined by a variable,
eg an accounts receivables example, I am looking at difference between revenue recognition and cashflow in, so if you get cashflow in the same month as revenue in formula would be
Cashflow:Month[THIS] = Revenue:Month[THIS]
If it was 1 month behind it would be
Cashflow:Month[THIS] = Revenue:Month[THIS – 1]
etc. i would like to be able to make the offset (like the -1 above) a variable, so formula would be
Cashflow:Month[THIS] = Revenue:Month[THIS – [i:201o69c5]referencevariable[/i:201o69c5]]
where referencevariable is an input item on another matrix.
When I input the formula like the above I get an error (see attached example). Is my syntax wrong? If this isn’t a natural capability of Quantrix has anyone figured out how to do this?
Thanks
Hi, jlin,
As far as I understood you, your model should calculate:
1. A shift the list of initial data by the quarters and by years (by means of the valueat());
2. The sums of data by years (YTDs).
I provide you my version of the solution of your task. This model is based on all those recommendations, which I described earlier in this thread. I hope, that it will help you.
However, if I gave you answers to the wrong questions (i.e., if i misunderstood you), then you need to apply directly either to the support team or to the recognized experts (Michael Salisbury, Luca Erzegovesi, Dominik Holenstein, Steve Bailey).
Hi, was there ever a resolution to the original question? That is, using a referenced variable inside a recurrence, as in:
ABC = DEF:Year[PREV – X]
where X is a referenced variable in another matrix.
It was mentioned there was going to be a new function implemented that would address this. Was this the ValueAt() function, or something else?
Thanks,
Rich
Sau,
Thank you again for your generosity. I understand your point – in order to use ValueAt() I think I need to change the way I make my models, to break down my matrices into lots of smaller, simpler pieces, and keep the Input matrices “clean” of all summary items. Although that seems to defeat the purpose of Quantrix’s feature of creating summary items automatically just by clicking a button, since doing so would automatically add that summary item to the “source” matrix, which would cause ValueAt() to misbehave.
For best ease of use and and understandability, I [b:3g8h200s]still[/b:3g8h200s] think ValueAt() should still be redesigned to support the “special case” that is Timelines and Summary Items, and Quantrix should consider adding an additional kind of operator that behaves like # but can skip summary items AND also support Timelines (e.g. innately understand that Months are grouped into Quarters, Quarters into Years etc. and traverse the sequence no matter how the user moves around the dimensions. But that is a different question.
Thank you again for your great example!
Thank you Sau for your reply. I’m confused by your statement “don’t add in this matrix aggregation on upper specification level” … do you mean to NOT add the aggregation (e.g. summary items) in the source matrix, and only add the aggregation (e.g. summary items) in the final matrix where I am going to “import and offset” the data from the source matrix? Or do not have multiple categories in a source data matrix? I’ve attached a sample model to implement the first suggestion above but I don’t see how it solves any problem. Can you see what I am doing wrong?
In the models you shouldn’t mix different levels of calculations in one matrix. If you define calculations on lower specification level, don’t add in this matrix aggregation on upper specification level. Instead define a special matrix for aggregation only on upper specification level.
Function [b:3hmfezpe]ValueAT()[/b:3hmfezpe] operates only with a [b:3hmfezpe]Matrix Cell Position Pointer[/b:3hmfezpe] (Index) and [b:3hmfezpe]Ranges [/b:3hmfezpe](Lists), but your Demand is the [b:3hmfezpe]ValueAT()[/b:3hmfezpe] Operations with a [b:3hmfezpe]Matrix Cell Position Senses[/b:3hmfezpe] defined by you. For an [b:3hmfezpe]Pointer [/b:3hmfezpe]there is no difference between an ordinary and an aggregation Items.
Learn carefully the help section on this page [url=http://www.quantrix.com/help_resources/5.1/WebHelp/How_Quantrix_Accesses_Data.htm:3hmfezpe]http://www.quantrix.com/help_resources/5.1/WebHelp/How_Quantrix_Accesses_Data.htm[/url:3hmfezpe]
Sau,
In Quantrix 5.1 I’ve noticed that ValueAt() doesn’t seem to handle Summary Items so that the offset correctly “skips” them, nor does it span multiple items of a category. Suppose I have a Quarter + Year timeline, with summary item after Q4 called Sum of Quarters.
ValueAt() seems to do 2 things incorrectly: 1) it treats Sum Of Quarters as just another Quarter, so if I attempt to do any offsets, the Sum of Quarters may show up in a Quarter as if it were a Quarter value.
2) ValueAt() doesn’t seem to be able to span items of a category. E.g. if I use ValueAt() to offset data items by one quarter, instead of it offsetting the entire range from Y1 .. Y4 by one quarter, it merely offsets the data by one quarter [u:295p3n3h][b:295p3n3h]in each year[/b:295p3n3h][/u:295p3n3h].
A 3rd problem has to do with the “#” operator not being able to “skip” summary items properly, so something like #Month with a Year summary item will result in January of the next year reporting #Month = 13 instead of 12. (Maybe there is another Summary Item-aware operator that I should be using, but I haven’t found it yet)
To borrow your example, if you had a Year category that was the parent of your Month category, and a Sum of Months summary item after December, then
Cashflow = ValueAt( Revenue:Month, #Month – Assumption Input::Value:Cashflow Month Offset )
would result in Revenue:Month [b:295p3n3h][u:295p3n3h]of each YEAR[/u:295p3n3h][/b:295p3n3h] being offset by[b:295p3n3h] #Month – Assumption Input::Value:Cashflow Month Offset [/b:295p3n3h], and the offset [u:295p3n3h][b:295p3n3h]would increase by 1 each year[/b:295p3n3h][/u:295p3n3h] after the 1st. Have you seen / solved this problem? Or am I just horribly misusing this function??
When will be released QM5.1 CashFlow can be calculated by using ValueAT() function:
Cashflow = ValueAt( Revenue:Month, #Month – Assumption Input::Value:Cashflow Month Offset )
So wait for QM5.1 release
