3 Replies Latest reply: May 31, 2013 8:15 AM by Ivan Gadzhonov

# Weighted average for every quarter using the last 4 quarters

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

• ###### Re: Weighted average for every quarter using the last 4 quarters

See attached example.

• ###### Re: Weighted average for every quarter using the last 4 quarters

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??