Trying to aggregate based on a subset of data after a selection is made
In the attachment I sent there's one table. Using the ticker = 'EEM US EQUITY' and tenor = 'Tenor_1M' as an example:
For the date April 12/2018 (which corresponds to rownum = 1946) I want to calc the min(varswap_volvalue) using 252 historical observations. Therefore, I want the min value for this field using for those 2 dimensions where the rownum >= (1946 - 252) And rownum < 1946.
In this example I would expect the min(varswap_volvalue) for these dimensions to = 13.4288 as in the attached excel.
Since the underlying table has data for many dates the user would typically select a specific date to look at at any point in time. I need to calc of the min value to remain relative to the what the use has selected NOT be impacted. In other words, if hte user picks April 11/2018 then the expression would need to calc the min value for the rownum >= 1945 - 252 and rownum <1945.
This is just a single need I have for this logic...I also need to be able to calc the max/std dev/med/fractile/count # of times where there varswap_volvalue for the specific date selected is > values for the same field in that rolling 252 day period.
