Calculated Control in Group Footer - modified average calculation

[jcanady]

Good Evening everyone! I have an unusual calculation that I need to make in a group footer. I have been toying with this for a while now, and I now know a ton of ways NOT to get it working.
I need to calculate ((2*Q4 data)+Q3 data + Q2 data)/4. As an example:


Q1 = 0.060
Q2 = 0.075
Q3 = 0.085
Q4 = 0.073

The Calculated data should then be: ((2*0.073)+0.085+0.075)/4=0.0765

I am having difficulty getting this to work as a calculated control... Any thoughts on how to proceed? Or what other information would make it easier to evaluate? I am trying to strip all the data out of my db to make it postable... currently though there is over 20 mb of data and 2 of "front" end...

Thanks!
Jm

[June7]

Why doesn't it work - error message, wrong results, nothing?

Q1, Q2, Q3, Q4 are fields of same record?

Calculations in header/footer should be aggregates, like: Sum(Q1)

[jcanady]

Thank you for your reply! I knew that I should have taken a few more minutes to describe my problem...

My data is structured more like: SampleDate, Result.

There is one sample taken each quarter. So the calculation that I am performing are on separate records, not different fields in the same record.

So using the data from above I would have something like this:
Sample Date Result
1/1/2013 0.060
3/1/2013 0.075
6/1/2013 0.085
9/1/2013 0.073

Thank you again for the reply and I hope this clarifies my question.
Jm

[June7]

There is one sample of what?

Why do you multiply Q4 by 2? Why is Q1 not included?

Aggregate calc on a field usually would not require a value in one record to be treated differently from the other records. Normally, the need is to simply add all the values or average or standard deviation or max or min. These calcs are done with aggregate functions: Sum(fieldname), Avg(fieldname), StDev(fieldname), Min(fieldname), Max(fieldname).

If you want to provide db for analysis, follow instructions at bottom of my post.

[jcanady]

The reason for the 2xQ4 is it is a regulatory requirement. It is designed to be a look ahead to the next quarter. Compliance with this particular regulation is on a Running Annual Average. The Thought process is if you look ahead and you will be out of compliance in the next quarter based on getting the same results you did in the "current" quarter you will have time to react and potentially avoid a compliance issue.

I will look to see if I can strip the db down far enough to get to the 2mb limit and upload it later today.

Thank you again for your interest and you help.
Jason

[jcanady]

I may have found a solution to this vexing problem... It is not pretty, but it seems to be working:

Sum([qryTTHMTotal_Report].[SumOfResult])+DMax("qryTTHMTotal_Report.SumOfResult","qryQuick DDBP_Stage2","sampledate = #" & Max([qryQuickDDBP_Stage2].[sampledate]) & "#")-DMax("qryTTHMTotal_Report.SumOfResult","qryQuickDD BP_Stage2","sampledate = #" & Min([qryQuickDDBP_Stage2].[sampledate]) & "#")/4)

Since I cannot really perform aggregate functions in the group footer, I sum the values, subtract the first quarters value (DMAX where the date is the smallest) add a second third quarter (DMAX where the date is the largest) and then divide by 4. As I almost always have 4 quarters of data displayed this seems and the result is designed to tell me if in the next I may be out of compliance I think this will work.

If anyone can see of a cleaner way to get to this result, I would appreciate any feedback I can get. Otherwise, as this seems to get the job done, I will mark this thread solved later this weekend.

Thanks! Jm

The reason for the 2xQ4 is it is a regulatory requirement. It is designed to be a look ahead to the next quarter. Compliance with this particular regulation is on a Running Annual Average. The Thought process is if you look ahead and you will be out of compliance in the next quarter based on getting the same results you did in the "current" quarter you will have time to react and potentially avoid a compliance issue.

I will look to see if I can strip the db down far enough to get to the 2mb limit and upload it later today.

Thank you again for your interest and you help.
Jason