Calculate YTD and Rolling-12 Months Totals?

[McArthurGDM]

I have a table of values called ValuesT.



There are fields Type, Dates, and Values.

I want to be able to give the YTD or Rolling 12-Month totals for any given Type in a query. I understand that 2 separate queries will be needed for the two separate calculations. How is this done??

Thanks for your help!

[CJ_London]

depends on how your data is organised - and 'Type' is a reserved word so may cause problems if used but the principle would be

SELECT DISTINCT mytable.type, YTD.sumYTD, Roll.Sum12m
FROM (mytable LEFT JOIN (SELECT mytable.type, Sum(mytable.values) AS sumYTD
FROM mytable
WHERE (((mytable.dates) Between #01/01/2015# And Date()))
GROUP BY mytable.type)  AS YTD ON mytable.type = YTD.type) LEFT JOIN (SELECT mytable.type, Sum(mytable.values) AS Sum12m
FROM mytable
WHERE (((mytable.dates) Between dateadd("y",-12,date()) And Date()))
GROUP BY mytable.type)  AS Roll ON mytable.type = Roll.type

The two parts in red are subqueries calculating the ytd and 12month rolling

[McArthurGDM]

I'm not skilled enough to know what's going on in there.

Maybe you can just help me fill in the blanks so I can copy/paste or something. Or just use the fields I show below and I can fill them in later.

NVM that I said I only had 3 fields before, I have a few.


Year | Month | Type | SubType | Value


I want to group based on Type and SubType and doing a YTD and Rolling-12 calculation based on the Year and Month.

ALSO, IF POSSIBLE:

If there ARE NOT 12 months to "roll" on for a particular month, to show a "-" or something to signifiy that there are not 12-months to roll on.


I'm really just not good enough to see what's going on in there.

[CJ_London]

OK create this query

SELECT mytable.type, Sum(mytable.values) AS sumYTD
FROM mytable
WHERE (((mytable.dates) Between #01/01/2015# And Date()))
GROUP BY mytable.type

and save it as qryYTD

then create another query

SELECT mytable.type, Sum(mytable.values) AS Sum12m
FROM mytable
WHERE (((mytable.dates) Between dateadd("y",-12,date()) And Date()))
GROUP BY mytable.type

and sat it as qry12Month

then create a third query

SELECT DISTINCT mytable.type, YTD.sumYTD, Roll.Sum12m
FROM (mytable LEFT JOIN qryYTD ON mytable.type = qryYTD.type) LEFT JOIN qry12Month ON mytable.type = qry12Month.type

Year | Month | Type | SubType | Value

of these, all but SubType are reserved words and will cause problems. After 127 posts you should know this - and I'm sure you can reinterpret 'Value' as 'Values' etc. Also I suggest you put the dates back in - why did you change it?

rename them and tell me what they are now called and get the above three queries working and perhaps I can help with

If there ARE NOT 12 months to "roll" on for a particular month, to show a "-" or something to signifiy that there are not 12-months to roll on.

[CJ_London]

thread restarted here

https://www.accessforums.net/queries...als-51616.html

regret I will not be responding

[McArthurGDM]

Thank you for your solution - that is, by far, the only reasonable (simple) solution on the net that I could find for MS Access.

I used the field names "Month", "SubType", etc... just to illustrate what the fields were. The real names are irrelevant to any forum reader. I created the second post because I realized that this was a Query problem vs. just general Access. Though, I should have deleted it.

The dates should be in there, I forgot them when I wrote the thread. The month and year is pulled from the date but the date field is needed for the query.

Thanks again,

[McArthurGDM]

Actually, when I took a second look at the code and implemented it I realized the code was only half what I needed, which is my fault since I said the word "GROUP". What I meant for YTD and Rolling-12 Sum/Average was this:

Let me know if these make sense. The code is obviously going to be a bit more complicated.

---- EDIT: The Date field should be in there, assume it is. ----

*YTD: Assume these are the only data values we have

Type	SubType	Year	Month	Value	YTD
A	A1	2015	Jan	1	1
A	A1	2015	Feb	2	3
A	A2	2015	Feb	1	1
A	A3	2015	Mar	1	1
B	B1	2014	Nov	3	3
B	B1	2014	Dec	2	5
B	B1	2015	Jan	4	4

*Rolling-12 Month Sum/Average: Assume, again, 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

[McArthurGDM]

Here's a code that I gathered to get the YTD calculations to work:

I don't know how to get 12-Months Rolling out of this, though...

Mind you, those are reserved words and I just renamed them to make it more clear as to what it is.

SELECT b.Type, b.ID, b.SubType, b.Date, (select sum(a.Value) 
        from MyTable as a 
        where a.Months <= b.Months AND a.Years = b.Years and
              a.SubType = b.SubType and a.Type = b.Type
       ) AS ValueYTD
FROM ValueT AS b
GROUP BY b.Type, b.ID, b.SubType, b.Date, b.Months, b.Years
ORDER BY b.Type, b.SubType, b.Date;