I have an example of my own that uses rangeavg(above(Price,0,50)) to get a 50 day rolling average. I think it's a clean solution, and may be good enough in practice.
However, it's sensitive both to sort order and to selections. If you sort in the opposite order, suddenly you're looking at the future 13 weeks. If you select a specific week, it won't show you the 13 week rolling average for that week, but simply that week's value. A solution that fixes those problems is to use an AsOf table to manage your rolling period.
AsOfWeek, WeekType, Week
1,Current,1
1,Rolling,1
2,Current,2
2,Rolling,2
2,Rolling,1
3,Current,3
3,Rolling,3
3,Rolling,2
3,Rolling,1
etc.
Now build a pivot table like this:
Dimension 1 = AsOfWeek
Dimension 2 = WeekType // move to the top
Expression = avg(Value)
That doesn't allow sorting, I suppose. For a straight table it's a little more complicated, but not overly so:
Dimension = AsOfWeek
Expression 1 = avg({<WeekType={'Current'}>} Value)
Expression 2 = avg({<WeekType={'Rolling'}>} Value)
To generate the table above for the sample file:
AsOf:
LOAD
Week as AsOfWeek
,'Current' as WeekType
,Week
RESIDENT Data
;
CONCATENATE (AsOf)
LOAD
Week as AsOfWeek
,'Rolling' as WeekType
,Week + 1 - iterno() as Week
RESIDENT Data
WHILE iterno() <= 13
AND Week + 1 - iterno() > 0
;
I assume the real situation is more complicated, though, and wouldn't use this exact script. But the idea is you generate the table in script. Eh, I guess I might as well post it now that I've built it to make sure I wasn't getting anything wrong.
