Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    5

    Unanswered: averging data over a 3 month period

    I am trying to generate a rolling 3 month average of data based on the report date. So if I ran a report on say May 15th it would grab the data and give the average amount used for May, June and July.

    The data I am pulling from is based on per month usage.
    QTY_USAGE_MO_01
    QTY_USAGE_MO_02
    QTY_USAGE_MO_03
    QTY_USAGE_MO_04
    QTY_USAGE_MO_05
    QTY_USAGE_MO_06
    QTY_USAGE_MO_07
    QTY_USAGE_MO_08
    QTY_USAGE_MO_09
    QTY_USAGE_MO_10
    QTY_USAGE_MO_11
    QTY_USAGE_MO_12

    I am using a start date and end date parameter.

    Any help would be appreciated.

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    156
    You want to look at using the DateAdd("M", 2, {Your_Date_Field}) funtion. There is a plethora of Date funtions in Crystal. You can also create groups with custom sorts on day variations, week, months, quarters etc.

    You might want to consider a record selection, something like this...

    Date({tablename.datefield}) in DateAdd ("m", 3, {?parameter_date}) to CurrentDate

    Also, your date filter, is it a timestamp, so there will be data 3 months ahead of your date filter? Look at Crystal's date functions, it has 0-31 days, 31-60 days, 60-90 days, days, weeks, months, quarters, years etc. All of which can be added to any date using dateadd()


    I don't have time to go into detail right now, but let me know how you get on and please post your solution if you get one. :-)
    Last edited by GhostMan; 05-07-08 at 04:11.
    rgs,

    Ghostman

  3. #3
    Join Date
    May 2008
    Posts
    5
    I am still having trouble with this. I just don't know enough about the programming to get it to work. Anybody have any ides?

    Thanks

Posting Permissions

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