# Thread: Count and Average Q

1. ## Count and Average Q

I am tracking Nat Gas prices at various utilities. Every month the price changes and I log the new rate (note: I have between 15 and 60 months worth of data, per utility)

I want to create a report that shows the gas rates for the past 12 months than shows the average over the same period.

Q - How do I get the report to only display the past 12 entries per utility and make the average for those 12 entries?

Jim

2. ## RE: Count and Average Q

"Every month the price changes and I log the new rate" - so you enter an new record with the date?

Without knowing if you record the price on the same day each month, always, I would have to recommend that you create a duplicate of your table, minus the records, and then use xbasic to order the records in the original by facility then descending by cdate(yourdatefieldname).

Then fetch through the original table copying 12 records for each facility to the new table and print the report based on the new table.

3. ## RE: Count and Average Q

Yes, I enter a new record very month. The date is always for the entire month, i.e if the price changes on the 15th, it will be retroactive to the 1st.

The table is indexed on Date, so in theory, I could look at the latest date (the month) and count 12 months back...

But can it be done without xbasic?

4. ## RE: Count and Average Q

Probably, but I still see a lot of questions.

What if you try to run the report on June 4, 2004 and haven't yet made entries for June. You'd need to look for records for May 2004 back to June 2003.

But if records had been entered for June 2004, you'd want records for June 2004 back to July 2003.

So you first need to query on the latest dated records, branch to one of two options for the month of the latest records (current month or previous month). Then decide if the latest month is December (if so you can use the current year number, otherwise you need the previous year number for some of the months).

Then query for dated records between twelve months back to latest records. Then print a report based on current selection of records......

5. ## RE: Count and Average Q

I did find that you could eliminate some of the steps by using action scripting to set/create a variable with values from an expression (set the scope to global).

Expression is

=tablemax("yourtablename",".t.","yourdatefieldname")

which returns the date in the latest records. The rest could just be filter construction in the report.

In pseudo code the filter would read like

if the month of the variable is december, filter for dates in current year

otherwise filter for dates between month number + 1 of previous year through month number of current year

6. ## RE: Count and Average Q

Thanks! I'll give it a try

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•