Timeline recursion does not skip summaries

5.38K viewsFormulas and FunctionsRecursion Timelines

The enclosed model contains a timeline (Year/Month). In the “Closing UPR Assumption” matrix, I compute the “Calculation” item using a weighted average of the last twelve “Base” items (the weights are in a matrix called “Closing UPR Rate”). The formula is as follows:
Calculation:Month[THIS] = sumproduct(Base:Month[THIS-11] .. Base:Month[THIS],Closing UPR Rate::Closing UPR Rate%)
The problem is that the “Base:Month[THIS-11] .. Base:Month[THIS]” recursion does not skip the Sum of Months, which is a summary item. The result becomes totally distorted as a consequence.
I would like to recursion to skip the “Sum of Months” items in the Base item row. The recursion should compute using only genuine months.
Is there a way to do this ?
Thanks a lot

jlin Answered question November 28, 2018

There is some consensus among Quantrix long-timers that summary items are a pain to work with.  I reckon that this is a one example of how summary items can be cumbersome.
You can solve your problem, I believe, generally by creating a few helper items then using the SELECTBETWEEN function within your sumproduct.
The helper columns you need to create are:
Date – return the date of the current month and year
Month Offset – return the start date of x months back (in you example x= 11)
Then use in the SELECTBETWEEN  lookupvalue1 = DATE, lookupvalue2 = Month Offset

In the attached model Matrix1 is an example.

QuantrixAuthority Answered question November 12, 2018
You are viewing 1 out of 3 answers, click here to view all answers.