12 Month Rolling Average 2

[Swi]

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

 

[Andrzejek]

How about something like:
[tt]
Select SKU, AVG(plannedQty) As AvgQty
From SomeTable
Where shipment_date BETWEEN DateAdd("yyyy", -1, Date) And Date
Group By SKU
[/tt]

---- Andy

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

 

[mikrom]

You can use the DAVG() function in your query as shown in this video:

https://www.youtube.com/watch?v=aHWLCydz90o

 

[Swi]

Hi Andrzejeck,

Is this calculating the average by day?

Thanks.

Swi

 

[Andrzejek]

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:[pre]
shipment_date SKU plannedQty
1/1/2020 123 20
2/2/2020 654 400
2/2/2023 789 5[/pre]

and the outcome you desire.

---- Andy

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

 

[dhookom]

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

 

[Swi]

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

 

[dhookom]

Why would 1/24 = 50?

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

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;

[pre]
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[/pre]

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

 

[Andrzejek]

Swi, now you are changing your requirements.

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

Whatever happened to SKU's

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

 

[Swi]

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

 

[Andrzejek]

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 :-().
We would be done a long time ago if you would state just 2 points with an example:
[ol 1]
[li]This is what I have[/li]
[li]This is what I want[/li]
[/ol]

---- Andy

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

 

[Swi]

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

 

[dhookom]

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.

[pre]
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[/pre]

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

 

[Swi]

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

 

[dhookom]

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

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");

[pre]
InventoryItem Total Usage Average Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Test1 1831 610.33 497 1334
[/pre]
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

 

[Swi]

Thank you for your help.

Swi