Rolling 3-Month Calculation (Set Analysis)

[Anonymous]

Hello,

I am currently trying to use set analysis to calculate the rolling cumulative 3-month return, standard deviation of returns and average market value for various accounts (A through F). In the attached file, I am using rangesum, rangestdev and rangeavg for those calculations, respectively, but am looking to use set analysis instead so that when I filter my scatter plot chart by date, the rolling calculations don't change.

For example, if you clear all filters, as of 5/31/2017, the rolling 3-month return for account B in the table is 18.69% (which is the correct value). However, if you select 5/31/2017 in the calendar drop down, the 3-month rolling return in the table changes to 2.56% (which is wrong). Same problem with standard deviation and market value calculations. This is likely because I am using the range functions instead of set analysis to specify a rolling date range.

Any suggestions would be greatly appreciated.

Thank you!

[sunny_talwar · Accepted solution]

This?

[Anil_Babu_Samineni]

Try this?

RangeSum(Above(Only({<Date=>}Return),0,3))

[Anonymous]

Thank you Sunny! Very helpful