Weighted average for every quarter using the last 4 quarters

[ivan_will]

Hi,

please find attached an xls file with an example in it:

the idea is as follows:

for every quarter, for every ID I want to have weighted average Number - so for 31/03/2013 it should be the following:

(Number*daysInQuarter  (31/03/2013) + 

Number*daysInQuarter  (31/12/2012) + 

Number*daysInQuarter  (30/09/2012) + 

Number*daysInQuarter  (30/06/2012)) /  (90+92+92+91) = 75

for the previous it should be :

(Number*daysInQuarter  (31/12/2012) + 

Number*daysInQuarter  (30/09/2012) + 

Number*daysInQuarter  (30/06/2012) + 

Number*daysInQuarter  (31/03/2012)) /  (92+92+91+91) = 65

And I want to have this in the script - the same as the below table. So if you have something similar it would be a help. Thanks!

ID	Date	Number				daysInQuarter
we	31/03/2011	10				89	#VALUE!
we	30/06/2011	20				91	#VALUE!
we	30/09/2011	30				92	#VALUE!
we	31/12/2011	40				92	25.13736264
we	31/03/2012	50				91	35
we	30/06/2012	60				91	44.94535519
we	30/09/2012	70				92	55
we	31/12/2012	80				92	65.05464481
we	31/03/2013	90				90	74.95890411

Thanks in advance!

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[ivan_will]

yes, it works fine, but now another question:

if we have another date with change - let's say 21/03/2013 - number= 77

how to calculate the weighted average in qv script for the quarter/s??

regadrs

[ivan_will]

??