3 Replies Latest reply: Jul 3, 2012 4:43 AM by Herman Li

n Month average with multiple dimensions in straight table

Hi all,

I need to make a summary table that calcalate the 3 months average with mulitple diminsions in straight table is as below:

 Date Region A 3 Month Avg(A) (Explaination) 2011/11 A 217.9 72.6 (0 + 0 + 217.9)/3 2011/12 A 275.6 164.5 (0 +217.9 + 275.6)/3 2012/01 A 457.8 317.1 (217.9 + 275.6 + 457.8)/3 2012/02 A 456.1 396.5 2012/03 A 477.1 463.7 2012/04 A 357.7 430.3 2012/05 A 0 278.3 ( 477.1 + 357.7 + 0)/3 2012/06 A 0 119.2 ( 357.7 + 0 + 0)/3

I got the Date, Region and A and I need to calculate the average of A (3 Month Avg(A)). I have make a explaination column in the above example to make it clear what i want to do.

As it is multiple dimensions, I cannot use rangesum & above and i tried to use aggr but no joy. Also, by input a int, it can calculate any month avg, so..I cannot hard code the formula just calculate 3 months..

Best Regards,

Nick

• Re: n Month average with multiple dimensions in straight table

I'm in the train so can't test this now but I can't see why RangeSum() and Above() won't work:

RangeSum(Above(Sum(A),0,vSteps)/vSteps

Where vSteps is your input variable.

Hope this helps,

Jason

• Re: n Month average with multiple dimensions in straight table

Missed off a parenthesis...

RangeSum(Above(Sum(A),0,vSteps))/vSteps

• Re: n Month average with multiple dimensions in straight table

Try this:

RangeSum(above(TOTAL Sum(A),0,3)) / 3