topic n Month average with multiple dimensions in straight table in QlikView

n Month average with multiple dimensions in straight table

Tue, 03 Jul 2012 07:00:01 GMT

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

Re: n Month average with multiple dimensions in straight table

Jason_Michaelides — Tue, 03 Jul 2012 07:53:15 GMT

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

Jason_Michaelides — Tue, 03 Jul 2012 07:54:03 GMT

Missed off a parenthesis...

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

Re: n Month average with multiple dimensions in straight table

Tue, 03 Jul 2012 08:42:29 GMT

Try this:

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