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!
