Timeline recursion does not skip summaries

3.76K 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

FYI if you have the need to do different offsets simultaneously, I would suggest calculating a single index value (that ignores all summary items). That way can support multiple, differently offset data references simultaneously.

jlin Answered question November 28, 2018

Dear Rich,
A big thank you, this is very instructive and a clever way to resolve the issue. I agree that summary items could be better configured to avoid such problems. I will try your solution, and I will also try another way, which is to unlink the timeline only in this calculation matrix (not ideal but easy) and removing the Summary in that calculation matrix. Again, thanks a lot for your help.

braddo Posted new comment July 20, 2020

Happy to try and help you become a Quantrix Master!


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