calculating averages

[[email protected]]

I have two tables connected in a set through an index key.. The primary table is "individuals" the child table is "reports_archive" . I have a report that lists the monthly reports grouped by individuals (each month a report record is made for each individual) hence you can easily see the individual and their monthly reports . At the footer I have the usual field totals calculated functions including an average for each field. What i want to do is generate an average for the last 3 months. A date field in the reports_archive is named dateindex. I was hoping to base the calculation through this using a date()-100 function but atlas I'm not smart enough.

I've tried all many of average() and TableAvg() options but can't seem to get it to calculate.

Any help would be much appreciated

DeanO

[Keith Hubert]

Re: calculating averages

Hi Dean,

Welcome to the Alpha Forum.

Can you have your report just for the last 3 Months?

[[email protected]]

Re: calculating averages

Thanks for replying, what I should have explained is that the report already has calculated averages for the months recorded, I WANT IN ADDITION to that ON THE SAME REPORT a calc expression showing the average for just the last 3 months and ultimately ALSO for the last 6 months - all using as a basis the dateindex field. Having the report just show the last 3 months or whatever is easily done but I don't want seperate reports - ie one for 3 months, one for 6 months etc.

Thanks again

[Tom Cone Jr]

Re: calculating averages

First thought here is that you could use three new calc fields in your report layout.

a) use tablesum() to compute the total for the desired date range
b) use tablecount() to compute the number of items in the desired range
c) divide (a) by (b) to get the average for the desired date range

The examples in the help system for the tablecount() function will be a big help. ( If I do say so myself. )

[Keith Hubert]

Re: calculating averages

Hi Dean,

Tom has recommended exactly what I would have done. The function tablesum() is very powerful and I use it a lot. Many thanks to Tom for it was he who created it.

I would suggest the filter date range to be Between(date,date()-100,date()). That way the report will always calculate the last 100 days from today's date.

[Tom Cone Jr]

Re: calculating averages

Keith, I'd love to take credit for tablesum(), but I can't. It's a built in function supplied by Alpha.

-- tom

[Keith Hubert]

Re: calculating averages

Not what is says in Wiki!!!!

[Stan Mathews]

Re: calculating averages

I believe that is in reference to the (marvelous) examples.

[Tom Cone Jr]

Re: calculating averages

Right. I contributed many of the examples in the discussion of "Tablecount()" function.

[[email protected]]

Re: calculating averages

First thought here is that you could use three new calc fields in your report layout.

a) use tablesum() to compute the total for the desired date range
b) use tablecount() to compute the number of items in the desired range
c) divide (a) by (b) to get the average for the desired date range

The examples in the help system for the tablecount() function will be a big help. ( If I do say so myself. )

Thanks again for your help.. How do I incorpate the group in the formula?

For example the total for this group is:

total(Reports_Archive->Hours,GRP->Group 1)

of course this calculates all the months for that individual. Your suggestion which looks like this:

tablesum("reports_archive.dbf", "dateindex >= date()-100", "hours ")

returns all the hours for all the individuals on or after 100days proir to todays date. How to I limit it to work only within that grouping?

Thanks

DeanO

[Keith Hubert]

Re: calculating averages

Hi Dean,

I imagine that you just drag your new calculated field to the footer of the group and select total from the dialog. This in fact would create a another new calculated field. No need for you to do anything.

[Tom Cone Jr]

Re: calculating averages

DeanO, pls furnish some sample data and your report layout. i.e. attach a zip file containing tables (DBF, CDX, FPT files) with sample data, and the dictionaries for your set.

If you're still having trouble with this let us know how we can contact you. Use the private message feature of the message board if you wish.

[[email protected]]

Re: calculating averages

Hi Dean,

I imagine that you just drag your new calculated field to the footer of the group and select total from the dialog. This in fact would create a another new calculated field. No need for you to do anything.

Thanks kieth

Unfortunately this doesn't help as the calculated field is already in the group footer

[[email protected]]

Re: calculating averages

Thanks Tom

If need be I can do this, however the DB is populated with sensitive data already which I can't share, and I'm loath to stuff around with it unless there is no other way.

Thanks
DeanO

[Tom Cone Jr]

Re: calculating averages

DeanO, click my user name in the panel to the left of this reply. Then send me a private message or e-mail. -- tom