Taking the Average difference between numbers 1

[mdcson]

My goal is to determine, first, the difference in dollar amounts between months and then calculate the average difference. I have the following data for example:

Month 1 $446.4
Month 2 $486.7
Month 3 $458.5
Month 4 $455.8

Each dollar amount is a result of a formula calculation where formula 1 is in reference to month 1 and so on.

I know that it is easy to get the difference by just using @formula2 - @formula1. However, the problem is that, for example, when you take @formula3 - @formula2, you get a negative number. I am aware of the ABS function that will give the absolute value, however, when I used it in this case, it was not working for me.

I wrote: abs({@formula2} - {@formula1}) and received back a number over 900!

Is there another way to utilize this function for this purpose or is there an entirely different solution?

 

[lbass]

What are the contents of {@formula1} and {@formula2}?

-LB

 

[mdcson]

@formula1 =
if {@ToDate}in dateserial(year(currentdate), month(currentdate)-13,1) to dateserial(year(currentdate),month(currentdate)-12,1)-1
and
{PatientSummaryTBMonthly.Hospital Abbreviation}in ["LJ","GH","ME","CV","EN"] then
{PatientSummaryTBMonthly.TB Period Charges}/1000000
else 0

@formula2 =

if {@ToDate}in dateserial(year(currentdate), month(currentdate)-12,1) to dateserial(year(currentdate),month(currentdate)-11,1)-1
and
{PatientSummaryTBMonthly.Hospital Abbreviation}in ["LJ","GH","ME","CV","EN"] then
{PatientSummaryTBMonthly.TB Period Charges}/1000000
else 0

@ToDate is simply a formula that converts data in the tables to a date format that I can use.

 

[lbass]

These are detail level formulas, so if you wanted to show the results in the report footer, you would use formula like this:

abs(sum({@formula1})-sum({@formula2}))

-LB