Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Please kindly help.
Best Regards,
Nick
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
Missed off a parenthesis...
RangeSum(Above(Sum(A),0,vSteps))/vSteps
Try this:
RangeSum(above(TOTAL Sum(A),0,3)) / 3