Results 1 to 4 of 4

Thread: Revenue Trend

  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    In Access 2000 I have a rollong 12 months revenue data for my accounts. I was asked to add a column that shows a trend. Up, Flat, or Down. No numbers, just an indicator.
    I've used the trendlines in Excel but can't seem to figure how to calculate for just an indicator in Access.

    Any help woould be welcome
    Thanks,
    Scott

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by ready4data View Post
    In Access 2000 I have a rollong 12 months revenue data for my accounts. I was asked to add a column that shows a trend. Up, Flat, or Down. No numbers, just an indicator.
    I've used the trendlines in Excel but can't seem to figure how to calculate for just an indicator in Access.
    I would be against adding a column for this, as it seems like it should be calculated when you need it. But how do you calculate it? I haven't seen anything like a "trendline" in Access. So what constitutes Up, Flat, or Down?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mark,
    I was thinking that similar to the trendline in Excel if the last point of the hypothetical trendline in my table is higher than the starting point the trend would be "Up" and vice versa.
    But I can't simply take the first and last month's revenue and do a calculation because the months between them would not be figured into the trend.
    Excel computes the values for the trendline taking all the months into consideration.

    Scott

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Berridale, NSW, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is a linear regression exercise - I do not think Access has a function available, so you would have to build your own function.
    It is similar to the SLOPE function in Excel. The formula is in the following link:

    Excel Tutorial on Linear Regression
    phoenix.phys.clemson.edu/tutorials/excel/regression.html

    If VBA code is not your thing, you could do bits of this with update queries into a temporary table.
    You would need to set a start date and an end date for the data set

Posting Permissions

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