Using ‘[THIS + Variable]’

Solved5.31K viewsFormulas and Functions

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?




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?

You are viewing 1 out of 10 answers, click here to view all answers.