Timeline recursion does not skip summaries

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
212 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

Answered question

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.

Answered question

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.

Answered question

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.

Answered question