Rolling 12 Months Total (1 Viewer)

[PaulSW]

Hi
I'm creating a simple Customer Invoice db for a friend.
I've created the customer & invoice tables, and a query "CustomerInvoice" that details each invoice per customer where recorded.
I've also created an aggregate query (based on the "CustomerInvoice" query) that sums the total amount of invoices raised each month (Col A to D of the attached)
However, I also need to calculate the rolling 12 months total (Col E of the attached).
This is where I would be extremely grateful on advice on the best method to calculate the rolling 12 months - it's above my knowledge base!!
Many thanks in advance.
Paul

 

[tvanstiphout]

Try a query along these untested lines:

select sum(TotalAmount) as RollingTotal, RYear, RMonth
from CustomerInvoiceAggregateQuery
where DateSerial(RYear, RMonth, 1) between DateAdd("yyyy", -1, Date) and Date
group by RYear, RMonth

DateSerial(RYear, RMonth, 1) creates a valid date, so we can do date math like restricting to the last year.
DateAdd is used to do date math, like subtracting 1 year.

 

[arnelgp]

shouldn't your Rolling calculation starts from the lowest RYear and RMonth, going up?
this is what i have.

 

[PaulSW]

Try a query along these untested lines:

select sum(TotalAmount) as RollingTotal, RYear, RMonth
from CustomerInvoiceAggregateQuery
where DateSerial(RYear, RMonth, 1) between DateAdd("yyyy", -1, Date) and Date
group by RYear, RMonth

DateSerial(RYear, RMonth, 1) creates a valid date, so we can do date math like restricting to the last year.
DateAdd is used to do date math, like subtracting 1 year.

Hi Tom

Thanks for this suggestion. I had to make some slight tweaks as detailed below:

SELECT Sum(SumOfTotalCost) AS RollingTotal, RYear, RMonth
FROM sqInvoiceMonthTotal
WHERE (((DateSerial([RYear],[RMonth],1)) Between DateAdd("yyyy",-1,[RMonth]) And "DateSerial"))
GROUP BY RYear, RMonth;

but unfortunately it just returned each months total rather than a rolling 12 month total.

 

[PaulSW]

shouldn't your Rolling calculation starts from the lowest RYear and RMonth, going up?
this is what i have.

Thanks for this arnelgp but it is a cumulative total rather than a rolling/moving 12 month total. I have already already catered for this in my customerinvoice query.

 

[Gasman]

Hi Tom

Thanks for this suggestion. I had to make some slight tweaks as detailed below:

SELECT Sum(SumOfTotalCost) AS RollingTotal, RYear, RMonth
FROM sqInvoiceMonthTotal
WHERE (((DateSerial([RYear],[RMonth],1)) Between DateAdd("yyyy",-1,[RMonth]) And "DateSerial"))
GROUP BY RYear, RMonth;

but unfortunately it just returned each months total rather than a rolling 12 month total.
View attachment 112676

So what is "DateSerial" for?

 

[arnelgp]

cumulative total

then still i am right since you don't accumulate on top, like when you want to accumulate something from day 1 to day 5, you start with day 1 going up.

there are now 2 variations (2 separate queries, inceasing/decreasing accumulation).