Rolling-12 Month Sum/Average Calculation

[McArthurGDM]

I am trying to create two fields that calculate Rolling-12 Sum/Averages. This will be based on a given Type and SubType fields (these ARE NOT the actual names of the fields, it's just for example purposes).

NOTE: There should be a Date field in there with dates of the form mm/dd/yyyy, but I forgot to add them in.

Any help creating this is much appreciated. If you need more clarity on how this is computed, I'll be glad to clarify.




*Rolling-12 Month Sum/Average: Assume that these are the only data values we have. If there are not 12-Months to roll-on, then indicate with a "-".

Type	SubType	Year	Month	Value	Rolling-12 Sum	Rolling-12 Average
A	A1	2015	Jan	1	-	-
A	A2	2015	Feb	1	-	-
A	A3	2015	Mar	1	-	-
B	B1	2014	Nov	3	-	-
B	B1	2014	Dec	2	-	-
B	B1	2015	Jan	1	-	-
B	B1	2015	Feb	1	-	-
B	B1	2015	Mar	1	-	-
B	B1	2015	Apr	1	-	-
B	B1	2015	May	1	-	-
B	B1	2015	Jun	1	-	-
B	B1	2015	Jul	1	-	-
B	B1	2015	Aug	1	-	-
B	B1	2015	Sep	1	-	-
B	B1	2015	Oct	1	15	1.25
B	B1	2015	Nov	1	13	1.083333333
B	B1	2015	Dec	1	12	1
B	B1	2016	Jan	1	12	1

This is a rehash of this post, but I feel like I would have more success posting it in the query section:

https://www.accessforums.net/access/...als-51604.html

[ranman256]

I have a form that runs this rpt. On it is the starting date. The macro runs a series of append queries to the 'report table'.
much like your grid.
then it runs update queries to the items in the rpt tbl.

[McArthurGDM]

I have a form that runs this rpt. On it is the starting date. The macro runs a series of append queries to the 'report table'.
much like your grid.
then it runs update queries to the items in the rpt tbl.

Interesting. I could calculate the YTD value for that time frame, and then run the calculation again, and then append accordingly. Not bad...

Though, a short, simple (relatively speaking) would be nice.

[rpeare]

What is your intended output, if it's a report you can do the running sums just fine on a report without much difficulty at all. If you're attempting to build a query you can export that's a much more involved proposition.

[McArthurGDM]

What is your intended output, if it's a report you can do the running sums just fine on a report without much difficulty at all. If you're attempting to build a query you can export that's a much more involved proposition.

How do you create running sums/averages on a report without much difficulty?

[rpeare]

Actually found a way to do it in a query that's pretty painless my table name is TBLTEST

ID	MainType	SubType	MonthEnd	MonthValue
1	A	A1	1/31/2015	1
2	A	A2	2/28/2015	1
3	A	A3	3/31/2015	1
4	B	B1	11/30/2014	3
5	B	B1	12/31/2014	2
6	B	B1	1/31/2015	1
7	B	B1	2/28/2015	1
8	B	B1	3/31/2015	1
9	B	B1	3/30/2015	1
10	B	B1	5/31/2015	1
11	B	B1	6/30/2015	1
12	B	B1	7/31/2015	1
13	B	B1	8/31/2015	1
14	B	B1	9/30/2015	1
15	B	B1	10/31/2015	1
16	B	B1	11/30/2015	1
17	B	B1	12/31/2015	1
18	B	B1	1/31/2016	1

This is my table, note the field names are different than yours to prevent using reserved words

Create this query:

SELECT tblTest.ID, tblTest.MainType, tblTest.SubType, tblTest.MonthEnd, tblTest.MonthValue, DateAdd("yyyy",-1,[monthend])+1 AS YearStartFROM tblTest;

Name this query QrySub

Create this query:

SELECT qrySub.MainType, qrySub.SubType, DatePart("yyyy",[qrysub]![monthend]) AS YearSort, DatePart("m",[qrysub]![monthend]) AS MonthSort, MonthName(DatePart("m",[qrysub]![monthend])) AS MonthDesc, qrySub.MonthEnd, qrySub.MonthValue, Sum(IIf([tblTest]![monthend] Between [qrysub]![yearstart] And [qrysub]![monthend],[tbltest]![monthvalue],0)) AS Rolling12Sum, Sum(IIf([tblTest]![monthend] Between [qrysub]![yearstart] And [qrysub]![monthend],1,0)) AS Rolling12Count
FROM qrySub LEFT JOIN tblTest ON (qrySub.SubType = tblTest.SubType) AND (qrySub.MainType = tblTest.MainType)
GROUP BY qrySub.MainType, qrySub.SubType, DatePart("yyyy",[qrysub]![monthend]), DatePart("m",[qrysub]![monthend]), MonthName(DatePart("m",[qrysub]![monthend])), qrySub.MonthEnd, qrySub.MonthValue;

It has all the numbers you want except the average but that's just division by two of the fields when you go to produce a report.

[McArthurGDM]

I see what you did, unfortunately, it didn't produce the desired results.

I think when I mention the term "Rolling-12" people get the concept confused with what I am implying. I hope this helps:


Consider a specific type and sub-type and take a given month of any given year, say January 2015 of that type and subtype. Take that month and the previous 11 months - all the way back to February 2014 (if applicable) - and sum the months. When you go to the next Month, say February 2015. Take that month and the previous 11 months - all the way back to March 2014 (if applicable) - and sum the months. Etc...

If there are NOT 12 months to "roll" on, indicate with a "-". This means that there were not a total of 12 months to create a Sum.

Does this make sense? Again, assume this is all the data we have. Here is a matrix that hopefully helps:

Type	SubType	Month	Year	Value	Rolling-12 Sum
A	A1	Jan	2014	20	-
A	A1	Feb	2014	19	-
A	A1	Mar	2014	15	-
A	A1	Apr	2014	14	-
A	A1	May	2014	13	-
A	A1	Jun	2014	20	-
A	A1	Jul	2014	11	-
A	A1	Aug	2014	10	-
A	A1	Sep	2014	15	-
A	A1	Oct	2014	10	-
A	A1	Nov	2014	11	-
A	A1	Dec	2014	12	170
A	A1	Jan	2015	13	163
A	A1	Feb	2015	14	158
A	A1	Mar	2015	5	148
A	A1	Apr	2015	16	150
A	A1	May	2015	17	154

[rpeare]

I didn't misinterpret your rolling sum. The query works, the formatting is up to you on your report. you want to put a text string in what is a numerical calculation which you can not do in a query, that is formatting on a report. i.e. you have a text box with a formula like

=iif([rolling12count] < 12, "-", format([Rolling12Sum], "Currency")

in the control source of the rolling sum text box.

[McArthurGDM]

You'll have to forgive me, I didn't realize what the Rolling12Count column was for. That's impressive!

Thank you so much! I was trying to get a solution for this for weeks.