# Timeline recursion does not skip summaries

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

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.

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.

Regards

vpluchet,

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.