Rolling 12 Month Average 1

[mdcson]

I am doing a report that shows the number of financials that came in for an entire month over a 12 month span. The report will include one calculation that will calculate the rolling 12 month average of those 12 numbers. For example:

Jan-10 $1M
Feb-10 $2M
Mar-10 $2M
Apr-10 $1.5M
.
.
.
Dec-10 $4M

Rolling 12 Month Avg = ???

 

[mdcson]

Also, I forgot to add the other calculation that I need in the above post.

I also need a sum of a year to date total of the monthly dollars based upon our fiscal year which begins in October. Thus, for example, when the latest month is October, the total year to date dollars will only include that month.

When the last month is September, the year to date total will reflect the full fiscal year, or all twelve months.

 

[lbass]

Can you explain in what sense this is "rolling"? Usually that refers to a calculation based on the previous x records, where you see a result for each record that reflects the previous x records before the current one, so that the average would be based on a different set of records each time.

Also, are you working with a date record that is of date datatype? In your example above, are the amounts summaries that you have calculated within the report? Or is your dataset made up of previously calculated monthly summaries presented as fields?

-LB

 

[mdcson]

Yes, this will refer to the last 12 records of a constantly changing set of records on a monthly basis. Thus, after every additional month's numbers are added to the database, the roll will, of course, change to the latest 12.

Yes, the date record is of a date datatype and the amounts summaries are Sums calculated in the report.

 

[lbass]

I see that I gave you the solution for a 3-month rolling sum recently in thread767-1635183. Did you try adapting that? Just change the 4 to 13 and the 2 to 11.

Regarding the totals in the fiscal year to date, you should be able to use a formula like this:

if year({table.date}+92) = {?FiscalYear} and
{table.date} < currentdate-day(currentdate)+1 then
{table.amt}

Then insert a sum on it to get the YTD total up through the last full month. Or use currentdate, if you want it up through today.

-LB

 

[mdcson]

Great thanks! I have now nearly completed my report but need one more calculation. All of my numbers are calculating, however, I need to have this column at the far right that shows the total amount for that particular month but for the prior year for comparison. All column calculations currently exist in the same GF which is what may be giving me issues in going back twelve months to capture the right dollar amount.

I have been playing with formulas like:

if {@ToDate}in dateadd("m",-12,currentdate) to
dateadd("m",-11,currentdate)
then
{PatientSummaryTBMonthly.TB Period Charges} as well as

if {@ToDate}in dateserial(year(currentdate), month(currentdate)-12,1)to
dateserial(year(currentdate),month(currentdate)-11,1) then
{PatientSummaryTBMonthly.TB Period Charges}

but keep getting zeros.



Month Amount Amount 1 year ago
Jan-10 $1M ?
Feb-10 $2M ?
Mar-10 $2M ?
Apr-10 $1.5M
.
.
.
Dec-10 $4M ?

 

[lbass]

If you had a group on a formula {@month} like this:

month({table.date})

...you could have shown both current and previous year values. But because of your other calculations (rolling average of last 12 months) which are based on your current grouping (date, on change of month), it might be easiest for you to insert a subreport to do that calculation. The sub should be linked to the main report on the above month formula and on a formula {@year}:

year({table.date})

Then change the subreport record selection formula to:

month({table.date}) = {?pm-@month} and
year({table.date}) = {?pm-@year}-1

-LB