Help needed with rolling (cumulative)AVG

[#1]

Hi everyone

Can someone help me do this operation?

the month is a dimension that it could be anything (year, week....)

and it is a range of months that i pick from my filtres "sheet" so it is not something static

Shall i use a pivot or a single table? and i have no problem if the calculations take place horizontal or Vertical .

I just want to do them [:P]

	This is one dimension Count = 1		Count = 2		Count = 3		Count = 4		Count = 5		Count = 6
Month	Oct09		Nov09		Dec09		Jan10		Feb10		Mar10
	Sum(Sales)	AVG	Sum(Sales)	AVG	Sum(Sales)	AVG	Sum(Sales)	AVG	Sum(Sales)	AVG	Sum(Sales)	AVG
CustA	30	30	65	47,5	89	61,33	45	57,25	15	48,80	45	48,17
CustB	40	40	35	37,5	55	43,33	55	46,25	25	42,00	78	48,00
CustC	50	50	28	39	10	29,33	65	38,25	35	37,60	65	42,17
					This is the SUM of (10+28+50)/3			This is the SUM of (35+65+10+28+50)/5			This is the SUM of (35+65+10+28+50+65)/6

THANK YOU VERY MUCH

[#2]

You've essentially described a pivot table.

Is there some part of the pivot table implementation you need help with?

[#3]

I just re-read your post, and I missed the 'cumulative' part of the request.

You could achieve the cumulative average using set analysis, and descriving the average calculation as the average of all sales for everything in the selection described as current and prior to, as opposed to the default functionality wich is to average only those fact values that meet both dimension criteria.

However, this won't actually do exactly what you've described. As opposed to the averages of the totals, you'll actually get the average at the individual sales level.

[#4]

Hi and thank you for the interest to help.

I have 1 question. As you mentioned the "prior to" and "current" i assume that is something like the "before" and "after" statements?

Is there any statement that collects the SUM of columns dimensions (before or after) instead than the previous only or next?

and if not is there any possibility to somehow do a smart Loop operation to gather these values?

[#5]

i found one nice trick so far. in order to get the cumulative count of "Dimensions" we can use the columnno(TOTAL)

Now what is left is to get the cumulative sum of sales through dimensions

[#6 · Accepted solution]

My friends thank you for the answer I found it

and it is working perfectly

rangesum(before(SUM([SALES]),0,columnno(TOTAL)))/columnno(TOTAL)