I can't seem to find any discussion on this, hence this new post.
I have raw data like this:
Day 5 = 30
Day 4 = 40
Day 3 = 25
Day 2 = 20
Day 1 = 45
I need a a report that shows the sum of the Past-N-Days window; e.g. N=2 and 3:
<Day>|<Past 2 days>|<Past 3 days>
Day 5 | 70 | 95
Day 4 | 65 | 85
Day 3 | 45 | 90
Btw, I have been attempting to solve the problem using SET analysis. The obstacle seems to be that it is impossible to aggregate data beyond the current dimension value. For example, I can't really get the data on Day 4 when my expression is dimensioned by Day 5. Or perhaps my approach in wrong.
You can still handle it with the AsOf table that I posted, with no changes. It manages all the connections with data instead of inter-record functions, so you can use all the normal aggregation stuff, such as count(distinct Rider). It does not have the performance issues on large data sets that the count(if()) approach would have. Basically, it takes the performance hit during the load instead, where it is usually more palatable. See attached.
(Edit: Improved performance of the AsOf table load, in case that's an issue. Now using fieldvalue() function instead of loading from a table. It executes significantly more quickly on large data sets, but is a bit more confusing than simply loading distinct.)
