RangeAvg of Previous periods in Table without displaying data
Hi Everyone,
Another question where i haven't been able to find an answer in these threads yet.
I know that if i have a table of values or a chart a can have a rolling average for past six period using the formula:
=rangeavg(above(sum(Sales),0,6)
But this only works when you are showing the data in chart.
Ideally i would want to show something like this:
Week
Product
Sales
Avg Last 6 Weeks $ Sales
Sept 7
Product 1
$100
$75
Sept 7
Product 2
$200
$100
Sept 7
Product 3
$300
$200
The goal of this is to create a poor-man's baseline for sales so when we put a product on promotion in a week we know what the incremental sales are by subtracting the Avg Last 6 Weeks $ Sales from the current weekly sales. the reason i don't want to show every product for every week, not only because it's simply too many to be useful, but i only want to show products that are on promotion for the week they are on promotion. I designate this with a "Promo" flag in the data. The goal is to be able to rank every promotion we did in a year and see what brought he most lift over the baseline.
Above() is a inter-record function and these functions could only fetch data which are within the chart (unless you extend the data with a virtual aggr-table). In your case I think set analysis is more suitable to calculate your needs, maybe something like this:
