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

    Weighted average for every quarter using the last 4 quarters

    Ivan Gadzhonov

      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!

       

       

      IDDateNumber daysInQuarter
      we31/03/201110 89#VALUE!
      we30/06/201120 91#VALUE!
      we30/09/201130 92#VALUE!
      we31/12/201140 9225.13736264
      we31/03/201250 9135
      we30/06/201260 9144.94535519
      we30/09/201270 9255
      we31/12/201280 9265.05464481
      we31/03/201390 9074.95890411

       

       

      Thanks in advance!