Moving annual total

[Guest]

Back once again...

Here is the following problem. In a query I want to calculate a moving
annual total in which the total should be the sum of the previous 12 months.
For example: the MAT of jan - 2005 is the sum of the period jan - 2005 to feb
- 2004.

In my query I have two years splitted by month. To calculate the MAT
I tried a sub query which is as follows:
MAT: (select sum(Value) from CY_TABLE as dupe where dupe.segment =
cy_table.segment and dateserial(dupe.the_year,dupe.month_of_year,1) between
dateadd("m",-12,dateserial(CY_table.the_year,CY_Table.month_of_year,1)) and
dateserial(dupe.the_year,dupe.month_of_year,1) )

This leads to the sum of the entire segment for the 2 years (every month the
same). When I replace the last
dateserial(dupe.the_year,dupe.month_of_year,1), with
dateserial(CY_Total.the_year,CY_Total.month_of_year,1) it gives me a running
total for all the periods (and years its a two year accumulated total).

Can anybody get this query up and running???

 

[Tom Ellison]

Dear Lee:

I don't have the database, so I can't get it up and running. Maybe I could
offer a hint at how you might begin.

Let's consider a column that has a MonthNumber. Let's use a simple formula
of Year * 12 + Month. Extract those values for your dates.

Use this in the generation of the filter you need for the running sum. For
an annual running total, you'd use those months from MonthNumber - 11
through MonthNumber of the current row. The subquery to sum these should
then be straight forward.

Tom Ellison

 

[Guest]

Thanks Tom,

I got it running now with your simple but effective solution.

Life can be so simple........