Rolling N Period Revenues with RangeSum, Above, & AGGR
I have an app with revenues by month (lowest level of granularity). Need to calculate rolling N periods on the fly, when the chart dimension is allowed to change with a cyclic group. The cyclic group "Periods" goes through 3 levels of reporting granularity: `Year-Period` -> 'Year-Qtr` -> `Year`.
The following formula works like a charm for the most granular setting (i.e. `Year-Period`):
where $1 is set to 12 for rolling 12, 6 for rolling 6, etc.
However, whenever the chart is cycled to `Year-Qtr` or `Year`, the calculations are incorrect, as the formula will aggregate 6 years above for $1 = 6 with Year selected, etc. I've considered some sort of workarounds such as dividing the $1 value by a factor based on the value of the cyclic group dimension (using `GetCurrentField(Periods)` in a conditional formula to provide a divisor for the N value passed into the parameter). However, this seems a bit clunky and presents some problems - for instance, if we want Rolling 6 months and the user selects `Year`, we'll get 0.5 as the row count in the RangeSum expression.
I've attached a sample QVD. I've been trying to crack this Rolling N calculation a number of ways, including with an AsOf Dates table...but I keep running into a wall whenever the granularity of the chart dimension changes.
I want the formula to return the correct rolling N-period amount regardless of what dimension the user selects. If it's a rolling 6-month, then I want it to show the sum of the prior 6 months of revenue as of the dimension date. If the user has selected Year in the cyclic dimension, then the chart should show rolling 6 months as of the end of the year (i.e. 12/31/20XX). If the user selects Year-Quarter in the cyclic dimension, the chart would show the sum of revenues for the past six months - i.e. sum of the current and prior quarters for each dimension value.