Moving Average

[knowledge76]

Hi Experts,
I tried the whole day to contruct a query that deliver me the sum of 3 months i.e the actual month + 2 months. The Table layout is

Month1 Cost Item Year Moving Sum (should be Output)
01/08/2010 20 A 2010 55
01/09/2010 5 A 2010 85
01/10/2010 30 A 2010 100
01/11/2010 50 A 2010 90
01/12/2010 20 A 2010 130
01/01/2011 20 A 2010 90
01/02/2011 90 A 2010 110

As for the last two records the 2 months (01/03/2011 and 01/04/2011) are missing, I have reverse the logic i.e I have summed the the Costs in the opposite direction.

In my Query I have a Field MYAVG which should calculate the moving average, but it is not working. I have used the following logic

DAVG("Cost","Query1","Item="'&Item&"' and Month1 between #" & Month1 &"#" & " and #" dateadd("m",2,Month1)&"#"):

Thanks in advance for your suggetions.

 

[Galaxiom]

Best to explain what you mean by "not working".

However I suggest you do this calculation using a self join rather than running a domain function for every record.