3 Month Moving average with three criterias

ovelove
ovelove used Ask the Experts™
on
Looking for a way to calculate a three month average with three criterias (Transaction date, Area and product id).

Current code is: MovingAverage: DAvg("Sales","Table1","Area ='" & [Area] & "' AND Month(format(TransactionDate,'mm/dd/yyyy') <" & (Month(Format([TransactionDate],'mm/dd/yyyy')+2)\3))

Struggeling with the Transaction date as I only receive an error in the query (the data is only monthly and all data is therefor set to 1/1/2015 for Jan and 2/1/2015 for feb).

I would also need product ID to be part of the criteria for the three month moving average.

Any suggestions?  I've attached the test database as well.
3-16-2016-11-30-03-AM.png
3-16-2016-11-30-18-AM.png
TEST--2---4-.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
using the domain functions in this type of query is going to be amazingly slow.  Generally, when I need to do something like this, I include the same table in the query twice and use a non-equi join to match three months worth of records from one table to a single record on the other record.

Select T.Area, T.ProductID, T.TransactionDate, T.Sales, SUM(T.Sales) as 3MonAvg
FROM Table1 as T
LEFT JOIN Table1 as TAvg
ON T.Area = TAvg.Area
AND T.ProductID = TAvg.ProductID
AND TAvg.TransactionDate >= DateAdd("m", -3, T.TransactionDate
AND TAvg.TransactionDate < T.TransactionDate
GROUP BY T.Area, T.ProductID, T.TransactionDate, T.Sales

You will not be able to do all of this in the query grid, and if you try to view it in the grid after you write the SQL you will get a message that Access is unable to represent the join in the grid.  So what I usually do is add the tables to the grid, add all of the columns, add the joins on Area, ProductID and Transaction date, then move to the SQL view and tweak the SQL.

HTH
Dale
Top Expert 2016
Commented:
Hi,

pls try this SQL
SELECT T1.ProductID, Min(T1.TransactionDate) AS MinOfTransactionDate, T1.Sales, Avg(T2.Sales) AS MovAvg, Count(T2.Sales) AS MonthsInAvg
FROM Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.ProductID = T2.ProductID
WHERE (((T2.TransactionDate)<=[T1].[TransactionDate] And (T2.TransactionDate)>=DateAdd("m",-2,[T1].[TransactionDate])))
GROUP BY T1.ProductID, T1.Sales;

Open in new window

Regards

Author

Commented:
Hi Rgonzo1971,
it works nicely but I'm missing the third criteria "Area". Is there an easy way to include it to the code?
Top Expert 2016
Commented:
then try

SELECT T1.Area, T1.ProductID, Min(T1.TransactionDate) AS MinOfTransactionDate, T1.Sales, Avg(T2.Sales) AS MovAvg, Count(T2.Sales) AS MonthsInAvg
FROM Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.ProductID = T2.ProductID
WHERE (((T2.TransactionDate)<=[T1].[TransactionDate] And (T2.TransactionDate)>=DateAdd("m",-2,[T1].[TransactionDate])))
GROUP BY T1.Area, T1.ProductID, T1.Sales;

Open in new window

Author

Commented:
Thanx, works perfectly

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial