Tricky sum set analysis excercise

[#1]

Hi,

need to find a way to sum as per the below example.

I guess some kind of set analyisis approach would be suitable but I cannot get it to work properly.

Pls see attached QV-example file and *expected result* excel file.

//O

[tresB · Accepted solution]

Expression for 1 Year:

RangeSum(Below(
FABS((SUM({<Type= {'1 year'}>}Volume))*(1-Period*vPrepayment/4)
-
(SUM({<Type= {'1 year'}>}Volume)))

/Period
,1,Count(Distinct Total QYear)))

Try similar for others

Edit: added 'Distinct'

[#3]

great, thank you!