Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002

    Unanswered: Calculating prior year-to-date figures on report


    I'm working on creating a report and I have run into a problem, which at
    least to me is complicated, however I hope that for someone out there it's a piece of cake.

    Let me first try to explain what I have to work from. (I get the data from another db via odbc, so i have no control over that part).

    I'm linking to a table containing sales figures. The column headings are:

    "mth_1" indicating previous month sales
    "mth_2" indicating second previous month sales
    and so forth until "mth_24" (the 24 previous months sales figures)

    If we are in Feb 2002 "mth_1" would indicate Jan 2002 and "mth_2" would indicate Dec 2001 and so forth.

    Hope I'm making sense so far.

    Now, I'm trying to come up with a formula that will calculate the previous
    year-to-date sales like such:

    If we are in March 2002 I want to calculate the sum of March 2001, Feb 2001, and Jan 2001. (I already have the current year-to-date sales figures to compare with, just not prior year-to-date figures).

    So, to accomplish the above example (if the current month was March 2002) I would take the sum of "mth_12" (march 2001), "mth_13"(feb 2001) and mth_14(jan 2001) to get the prior year-to-date sales.

    Or let's say the current month is Jun 2002, then I would take mth_12(jun
    2001), mth_13 (may 2001), mth_14 (apr 2001), mth_15(mar 2001), mth_16(feb 2001) and finally mth_17 (jan 2001) to get the prior year-to-date figures.

    The big problem (at least to me) is that I need a formula that will do this
    "on the fly". A formula that would check what the current month is and then calculate the corresponding previous months figures to come up with the prior year-to-date figures.

    I'm guessing I could use an If...Then...Else code like something like this (obviusly I don't know the syntax and to top it off I also don't know where to put the code once I have it):

    if month="january" then sum([mnt_12]) else
    if month="february" then sum([mth_12]+[mth_13]) else

    I'm stuck. Hopefully someone can help me get "unstuck".



  2. #2
    Join Date
    Nov 2001

    The best way to get "unstuck" is to re-design your table. Instead of having month of sale in columns you'd better have them in rows.

    Like this:

    1. SaleID
    2. DateOfSale
    3. AmountOfSale


  3. #3
    Join Date
    Feb 2002

    Well, I'm not sure how I could do that since the data is coming from another database (via ODBC links), and I can't manipulate it...

    Maybe there is a way that I'm not aware of?


Posting Permissions

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