Alpha Video Training
Results 1 to 6 of 6

Thread: Count and Average Q

  1. #1
    Member
    Real Name
    Jim Seidel
    Join Date
    May 2004
    Posts
    124

    Default 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?

    Thanks in advance

    Jim

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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. #3
    Member
    Real Name
    Jim Seidel
    Join Date
    May 2004
    Posts
    124

    Default 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. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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. #6
    Member
    Real Name
    Jim Seidel
    Join Date
    May 2004
    Posts
    124

    Default RE: Count and Average Q

    Thanks! I'll give it a try

Similar Threads

  1. Monthly average
    By edward.mattison@scbhn.org in forum Alpha Five Version 6
    Replies: 7
    Last Post: 08-19-2005, 12:52 PM
  2. Average
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 12
    Last Post: 12-10-2002, 11:56 PM
  3. Average Function.
    By Sharon Bracken in forum Alpha Five Version 5
    Replies: 6
    Last Post: 08-08-2002, 11:03 AM
  4. filtered average
    By Leonard Rosenstein in forum Alpha Five Version 4
    Replies: 4
    Last Post: 01-03-2002, 03:03 PM
  5. Average function
    By Bernard Posner in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-02-2001, 04:36 PM

Bookmarks

Posting Permissions

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