Yearly Cumulative Returns

[odassier]

Hi All,

I'm trying to build a table showing the yearly returns for my benchmark and my strategy. So my dimension is [Date.autoCalendar.Year] and what I want to see is the annual cumulative return for each year but the expression I am using for cumulative returns keeps aggregating on top of the previous year in the table instead of giving me the cumulative return just for that year in each row (see image below - columns "AMP Return", "Strategy Return" and "Active Return" all build on the previous year instead of giving me just the return for that year). 

Any ideas on how to modify the expression to get yearly returns instead of aggregate cumulative returns over the entire period?

Here is the expression I am using for the AMP Return column: 

FirstSortedValue(Aggr(exp(RangeSum(Above(log(1+[US-LMS_Return]), 0, RowNo()))) - 1, Date), -Date)

Thanks for the help.

Olivier

[sunny_talwar · Accepted solution]

How about this

FirstSortedValue(Aggr(exp(RangeSum(Above(log(1+[US-LMS_Return]), 0, RowNo()))) - 1, Year, Date), -Aggr(Date, Year, Date)

[bharadwaj]

Hi ,

Well ,I think this might help you in achieving your desired result.  The Expression I used to get the Cumulative Count is as Follows:

  Rangesum(Below(Sum(StrategyReturn),0,Count(total Year))) 

[odassier]

Thanks Sunny, that worked perfectly.

Olivier