×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

12 Month Rolling Average
2

12 Month Rolling Average

12 Month Rolling Average

(OP)
I have a Access database that has a shipment_date, SKU and plannedQty field.

I would like to do a 12 month rolling average of how much is being used of each SKU. What is the best way to accomplish this? thanks.

Swi

RE: 12 Month Rolling Average

How about something like:

Select SKU, AVG(plannedQty) As AvgQty
From SomeTable
Where shipment_date BETWEEN DateAdd("yyyy", -1, Date) And Date
Group By SKU

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: 12 Month Rolling Average

(OP)
Hi Andrzejeck,

Is this calculating the average by day?

Thanks.

Swi

RE: 12 Month Rolling Average

What do you mean by 'average by day'?
It calculates the AVG of plannedQty per SKU for last year.

If there is something else that you need, please provide an example of your data:
shipment_date  SKU   plannedQty
1/1/2020       123       20
2/2/2020       654      400
2/2/2023       789        5 

and the outcome you desire.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: 12 Month Rolling Average

I would use a subquery which might be a little more efficient than DAvg(). You mention “ 12 month rolling average of how much is being used” but you don’t have a field like “UsedAmount”.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: 12 Month Rolling Average

(OP)
Correct, I would want the average usage across 12 month prior to the current date.

Meaning.
11/3/23 - 10
11/20/23 - 20
12/1/23 - 10
12/15/23 - 40
1/15/24 - 50

So
11/23 = 30 total
12/23 = 50 total
1/24 = 50 total

I would want the average of these three months (in the real scenario I would look back 12 months).

I hope this makes more sense.

Thanks.

Swi

RE: 12 Month Rolling Average

Why would 1/24 = 50?

This gives you a rolling SUM and rolling AVG for each date

CODE --> SQL

SELECT tblSWI.Shipment_Date, tblSWI.PlannedQty,
 (SELECT Sum(PlannedQty)
  FROM tblSWI S
  WHERE S.Shipment_Date between DateAdd("m",-12,tblSWI.Shipment_Date) and tblSWI.Shipment_Date)
    AS SumLast12Months, 
  (SELECT Sum(PlannedQty)
   FROM tblSWI S
   WHERE S.Shipment_Date between DateAdd("m",-2,tblSWI.Shipment_Date) and  tblSWI.Shipment_Date)
    AS SumLast2Months,
  (SELECT AVG(PlannedQty)
   FROM tblSWI S
   WHERE S.Shipment_Date between DateAdd("m",-12,tblSWI.Shipment_Date) and tblSWI.Shipment_Date)
    AS AvgLast12Months,
  (SELECT AVG(PlannedQty)
   FROM tblSWI S
   WHERE S.Shipment_Date between DateAdd("m",-2,tblSWI.Shipment_Date) and  tblSWI.Shipment_Date)
    AS AVGLast2Months
FROM tblSWI; 

Shipment_Date	PlannedQty	SumLast12Months	SumLast2Months	AvgLast12Months	AVGLast2Months
11/3/2023	10		10.00		10.00		10.00		10.00
11/20/2023	20		30.00		30.00		15.00		15.00
12/1/2023	10		40.00		40.00		13.33		13.33
12/15/2023	40		80.00		80.00		20.00		20.00
1/15/2024	50		130.00		120.00		26.00		30.00 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: 12 Month Rolling Average

Swi, now you are changing your requirements.

Quote (Swi)

a 12 month rolling average of how much is being used of each SKU

Whatever happened to SKU's ponder

Again, show the sample of your data and what do you expect as the outcome.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: 12 Month Rolling Average

(OP)
Apologies, I did leave out the SKU part. You are correct. This would need to be done by SKU. Let me post back tomorrow when I am near a laptop as opposed to my phone. Thanks.

Swi

RE: 12 Month Rolling Average

So far, all replays are just guesses. We don't have any idea what you have in your table and what actually you want. I know it is clear in your mind (I hope), but that does not 'transfer' to ours (at least no to mine sad).
We would be done a long time ago if you would state just 2 points with an example:
  1. This is what I have
  2. This is what I want

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: 12 Month Rolling Average

(OP)
Yes, I understand your frustration however a sick child took the priority. I apologize for the wait. This is the end result.

Part Number Total Usage Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Average Monthly Usage
TEST1 1831 0 0 0 0 0 0 0 0 0 0 497 1334 916

This is the data.

inventoryItem plannedQuantity shipmentDate
TEST1 497 19-Nov-24
TEST1 1184 16-Dec-24
TEST1 150 18-Dec-24

This would be for every SKU though. I just provided a sample of 1 SKU called TEST1. It would be nice to have all months but I would just be happy to have the average monthly usage by SKU. Ex. - TEST1 -> 916

I hopes this helps illustrate my request. Again, sorry for the late reply.

Swi

RE: 12 Month Rolling Average

Please format your posts using TGML like both Andy and I take the time to do. Wouldn't Average Monthly Usage be 1831/12 = 152.583333333333? Again you confuse us by mentioning both 12 and 3 months? Is this always the previous 12 or 3 months regardless of crossing to a new year?

This looks much like a crosstab query.

Part Number Total Usage Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec  Average Monthly Usage
TEST1       1831        0   0   0   0   0   0   0   0   0   0   497 1334 916 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: 12 Month Rolling Average

(OP)
Hi,

It would be based on the previous 12 months but this inventory has only been received since November of last year. I mentioned 3 in this case which I could see would cause confusion. Thanks for your patience.

Swi

RE: 12 Month Rolling Average

Months with no quantity should not have 0, they would typically be null/blank.

CODE --> vba

TRANSFORM Sum(tblData.PlannedQuantity) AS SumOfPlannedQuantity
SELECT tblData.InventoryItem, Sum(tblData.PlannedQuantity) AS [Total Usage], Avg(tblData.PlannedQuantity) AS Average
FROM tblData
GROUP BY tblData.InventoryItem
PIVOT Format([ShipmentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"); 

InventoryItem	Total Usage	Average	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Test1    	1831	        610.33									                497	1334
 
You are going to get the average of the records, not the month. I you need the monthly average, the start with a totals query that groups by month. You may need to filter for the year or rolling year.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: 12 Month Rolling Average

(OP)
Thank you for your help.

Swi

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close