3 Month Moving average with three criterias
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","Are
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
Current code is: MovingAverage: DAvg("Sales","Table1","Are
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
Hi,
pls try this SQL
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;
Hi Rgonzo1971,
it works nicely but I'm missing the third criteria "Area". Is there an easy way to include it to the code?
it works nicely but I'm missing the third criteria "Area". Is there an easy way to include it to the code?
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;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial