Show rolling 3 periods of periodic average in a Straight Table

[#1]

Here's a simple data table with sales amount for two stores over 13 periods.

Period	Store1	Store2
P01	50	45
P02	40	65
P03	30	25
P04	54	15
P05	32	82
P06	68	32
P07	14	42
P08	8	35
P09	0	21
P10	9	53
P11	87	64
P12	25	87
P13	35	25

I need to figure out moving average of prior 3 periods for each period regardless the selection. Normally (in excel) I would calculate the average sales by period, next I would sum it up to get the rolling 3 periods. The result should be the last cell in this table (with P1 and P2 being incomplete). P4 Rolling 3Periods = $114.5 which is a sum of averages for P4, 3, and 2.

Period	Store1	Store2	Avg	Rolling3
P01	50.0	45.0	47.5	47.5
P02	40.0	65.0	52.5	100.0
P03	30.0	25.0	27.5	127.5
P04	54.0	15.0	34.5	114.5
P05	32.0	82.0	57.0	119.0
P06	68.0	32.0	50.0	141.5
P07	14.0	42.0	28.0	135.0
P08	8.0	35.0	21.5	99.5
P09	-	21.0	21.0	70.5
P10	9.0	53.0	31.0	73.5
P11	87.0	64.0	75.5	127.5
P12	25.0	87.0	56.0	162.5
P13	35.0	25.0	30.0	161.5

I've tried aggregation formula in combination with rangesum, but it just wouldn't seem to be able to disregard the current selections. Then I've also tried to create AsOf Table but the problem with this (I think) is that it wouldn't let me calculate the period average first and then do the sum.

I'm just not sure how to even think about the logic in the expression, so I'd appreciate anyone's input. As a side note, I do have a numeric id for each period in a separate table, which I also tried to use.

Thanks.

[Gysbert_Wassenaar]

I think so. You may want to include the other dimensions City, Account and Category in the Summary table too. If you want to make selections in those dimensions that would be necessary. If not you can use the more simple summary table with only Period as dimension. If you need to make selections in the Entity field as well then a summary table won't be of any use. You'll have to try to use a nested aggregation expression then in combination with an AsOf table. Perhaps something like sum(aggr(avg(Value),Entity))

[#3]

I actually have been trying to make it work with AsOf/TrailingP table and used the aggregation expression but it wouldn't work if I selected two periods from the [TrailingP] field. E.g. I chose P5 and P6 to show rolling for each, but the table only calculated it correctly for P6. This is what I used as an expression:

sum( aggr ( avg(Value),Period))

Do I need to call out the [Trailing] Field Selection to be disregarded as a modifier?

[Gysbert_Wassenaar]

That's hard to say without looking at your document. Can you prepare a sample? Read this document for how to create a non-confidential examle: Preparing examples for Upload - Reduction and Data Scrambling

And have you already read this document? -> Calculating rolling n-period totals, averages or other aggregations. Perhaps it helps with creating and using the AsOf table.

[#5]

Yes, I've read the document you mentioned. I've tried the solutions using the AsOf tables but I just constantly get stuck while trying to display more than 1 period for several selections in the "entity" field.

Let me prepare a sample and I'll post as soon as possible. Thank you for your help with this G Wassenaar I really appreciate your time.