3 Month Moving average with three criterias
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
Who is Participating?
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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;
it works nicely but I'm missing the third criteria "Area". Is there an easy way to include it to the code?
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;
Experts Exchange Solution brought to you by
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
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